Newly created sheet needs a Change Event (Excel 2003 & VBA)

Topic

New Reply

Hi All … in one of my last questions posted here, you guys taught me about the Private Sub Worksheet_Change procedure. Well, now the company wants more !!!

I have a workbook with VBA that creates sheets based on another workbook’s values/inputs. These sheets are not always created if they are not needed (based on the values/inputs in the other workbook).
However, in the sheets that are created, I need to add a Change procedure. Unfortunately, I do not know how to add a Change procedure if I cannot see the sheet first.

In the other post, where I learned about the Change procedure, I simply found the sheet under Microsoft Excel Objects and right-clicked and went from there.
Now, I don’t know where to go to place the code for a change event?

Does this make sense?

Thanks,
–cat

Reply | Quote

Replies

One way to do it would be to copy a sheet that already has the Worksheet_Change event procedure, then modify the contents of the worksheet as needed. That way, you wouldn’t have to mess with the code each time. Another way would be to create the code using VBA, but that is very technical, and rather tricky. If you’re interested, take a look at Chip Pearson’s Programming To The VBE.

Reply | Quote

Thanks, Hans. I hadn’t thought about putting it in the sheet I add first. Congrats on your 50,000 Post ! I know that I am personally glad that you are around when I have my questions!

About the Change procedure and performance, what type of hit will I take?

Thanks again,
Cindy

Reply | Quote

The general rule is that you should only put code in the Worksheet_Change event that is strictly necessary, since the code is run each time the user enters, modifies or deletes a cell value. But with modern-day computers, you can probably put quite a bit of code in there before you would start noticing a decline in performance.

Reply | Quote

I’m back …

I’ve placed code in a Change procedure sheet named “Generic” in an Excel Template file. I have another workbook macro that adds the Generic Sheet to the workbook, then proceeds to change the name of the sheet just added. Then, when my program first modifies the sheet, I get this error … “Compile Error – sub or function not defined”. I’m pretty sure it is because the name has changed from “Generic” to the appropriate name.

Is there a way that the Change Procedure can “know” the new name or a way to … I don’t know, get around this?

Thanks,
–cat

Reply | Quote

Does the code refer to the sheet name? It might help if you posted the code (in an attachment if it is long)

Reply | Quote

Hi,

The Change procedure does not refer to the sheet’s name (which starts out as “Generic” and gets changed later after it is added to another workbook).

All the Change procedure code does (or should do) is hide column D if cell D7 = 0 and unhides column D if cell D7 0.
I really don’t want to check for changes until the sheet has been added and set up corrected through my other macro. Hence the

I will try to get something to attach … the workbook that creates this file is fairly short, but it calls another workbook that is very large and, unfortunately, just about everything is confidential as I am working for a pharmaceutical company.

Thanks,
Cindy

Reply | Quote

Hi, (so glad this is Friday!)

It seems that the Change procedure doesn’t even work … so, I won’t worry about adding it to a new workbook and modifying it, yet.

I have attached the template. Again, I really really appreciate this forum’s help!

Thanks,
Cindy

p.s. I had to change the file from a XLT to a XLS since I couldn’t attach it.

Reply | Quote

Hi All,

I have re-attached another file.

For the Change procedure to work, does the user need to “manually” change the cells? … Or can the cells be changed by formulas?

Eventually these cells will be formulas (vlookups, indirect, etc) looking at another file, the datafile. The user changes that file, the values change in this sheet. When the values change so that D7=0, I wish to have column D hidden.

Thanks,
–Cindy

Reply | Quote

The Worksheet Change event does not fire when a formula recalculates. It will fire when a user or a macro changes the cell. You can use the Calculate event to catch a change due to a formula recalculation. However, the calculate event does not tell you what cell is recalculating, so the way you code it may be different.

Reply | Quote

Woo Hoo !!! That worked. I learned about the Change and the Calculate Event all in one week!!! I think I’m going to like this “Event” vba stuff. (Now I need to tackle the rest of the problem … )

Thank you all!
Cindy

Reply | Quote

Chip Pearson has a primer on Event Procedures that you may find interesting or useful.

Steve

Reply | Quote