Excel VBA

[GBalcom]

I'm fairly well versed in Access VBA, but I have a particular project that is MUCH better suited to a simple excel spreadsheet. I've never really done Excel VBA, and am wondering how to go about it. Does Excel VBA have "Event"s that run the code?

Below is a list of a few tasks I'd like to be able to complete:

• Highlight Colum D on a given row, when Colum Z equates to "1"
• After a "record" is completely filled in on a row, insert a row below it with pre-defined formulas for each cell (So I can have a total row without 100 empty rows in between
• Make certain colums on a given row locked or unlocked based on a checkbox in the same row (Modified checkbox)

I'm developing in Access 2013, for deployment to Access 2010 if it matters....(Long story)

If there are non-VBA ways to tackle the challenges above, I'm all ears...

 

[wiklendt]

Not very experienced with excel VBA either. On point 2, creating new rows, it would seem to me an easier pption od to mąkę your section od speadsheet into a 'table'. That way you have formulas in a whole column these get copied down automatically when new data is added to the next blank rów (and the tabe automatically expands to encompass that rów). Works well for me.

 

[GinaWhipp]

Hmm, seems to me you could use a combination of Access VBA and Excel Conditional Formatting on an Excel Workbook that you have set up as a Template. Would that work?

 

[Galaxiom]

Does Excel VBA have "Event"s that run the code?

Yes.

Open the VBA project for the Workbook and activate the appropriate worksheet module.

Drop down the left combo in the header of the module to "Worksheet".

Now the right combo in the header will show the list of events available for the worksheet.

 

[Gasman]

I tend to put my Totals row at the top of a sheet, referencing as many rows as required. That way I always get to see the totals without having to scroll all the time. Locking the first few rows helps with this.

I'm fairly well versed in Access VBA, but I have a particular project that is MUCH better suited to a simple excel spreadsheet. I've never really done Excel VBA, and am wondering how to go about it. Does Excel VBA have "Event"s that run the code?

Below is a list of a few tasks I'd like to be able to complete:

• Highlight Colum D on a given row, when Colum Z equates to "1"
• After a "record" is completely filled in on a row, insert a row below it with pre-defined formulas for each cell (So I can have a total row without 100 empty rows in between
• Make certain colums on a given row locked or unlocked based on a checkbox in the same row (Modified checkbox)

I'm developing in Access 2013, for deployment to Access 2010 if it matters....(Long story)

If there are non-VBA ways to tackle the challenges above, I'm all ears...

 

[GBalcom]

Thank you everyone for your responses so far. Here is where I'm at:

I've been able to figure out item 1 through conditional formatting...However, this cell is being highlighted to draw attention to the user to fill it in. Once they fill it in I would like to clear the highlight.

wiklendt: You recommended a table; I'm using one now and it helps alot! Thanks!

Looking into the conditional "locking" of a cell, looks like it will require some vba. The thing I'm stuck on is how to target any cell in a given column on the "change" event...especially when it's inside a table that grows as it's populated.

Also, Gina mentioned Access VBA....Unfortunately on this project I can't because it's a stand alone spreadsheet emailed to people who may or may not have access...and I'm afraid of those kinds of deployment issues

 

[Rx_]

You are also invited to share (attach) an Excel demo.
Use some comments in a cell to explain your objectives.
This site will probably force the extension be changed to .xls in order to attach.
A demo will go a long way to allow others to send back a solution.

 

[GBalcom]

Rx,
I'm now done with this challenge. I was able to accomplish more then I thought through conditional formatting, including highlighting a cell that needs input, then un-highlighting once it was populated. The only VBA I needed was to unlock 3 cells in a row, based on another cell (Modified) being set to "Yes".

I wouldn't mind knowing more about "targets" within the "Change" Event, but I was able to accomplish what I needed with a few simple IF statements.