Workbook_SheetChange evet Macro not working

**ALaws** · 03-05-2008, 01:55 PM

Hello everyone,

I found the following code elsewhere on the net, and have attempted to use it in my workbook to change the names of all 31 worksheets when a certain cell's (C7) value changes. The code looks good to me, so I cannot understand why it is not working. I am not getting error messages, just no changes to the sheet names.
I believe this may be due to the value of C7 on each sheet being dictated by a formula, so the value is not being changed with the sheet active, but indirectly with a formula that pulls the value from a cell on another sheet, and then adds.
(i.e. =TEXT('Populator Tools'!$C$25+1,"mm-dd-yy") )
Am I completely off base with this assumption, or on the right track; and if I am on the right track how do I fix this so it works?
Any advice will be helpful.
Code Below.

Thanks in Advance!

The Workbook_SheetChange Code Stored in This Workbook Module

Please Login or Register  to view this content.

Illegal Character Checker/Cleaner - Stored in Module 1

Please Login or Register  to view this content.

**mrice** · 03-05-2008, 02:29 PM

Have you considered driving your code from changes to $C$25 (assuming this is not a formula itself)?

**ALaws** · 03-05-2008, 03:29 PM

No, but I will try it and see if it works. --Thanks!

Originally Posted by mrice

Have you considered driving your code from changes to $C$25 (assuming this is not a formula itself)?

**ALaws** · 03-05-2008, 03:51 PM

Well, I tried it, and it almost worked....
It changed the name of the Populator Tools worksheet to the date entered into C25, which I didn't want to happen; and didn't change the other 31 sheets in the workbook, which is what I did want to happen, and is the intention of the code.
Thanks for trying though...
Anyone else have an idea?

Originally Posted by ALaws

No, but I will try it and see if it works. --Thanks!

**ALaws** · 03-06-2008, 03:57 PM

I posted this message yesterday. I got one suggestion, but it didn't work out. Any help will be appreciated.

Originally Posted by ALaws

Hello everyone,

I found the following code elsewhere on the net, and have attempted to use it in my workbook to change the names of all 31 worksheets when a certain cell's (C7) value changes. The code looks good to me, so I cannot understand why it is not working. I am not getting error messages, just no changes to the sheet names.
I believe this may be due to the value of C7 on each sheet being dictated by a formula, so the value is not being changed with the sheet active, but indirectly with a formula that pulls the value from a cell on another sheet, and then adds.
(i.e. =TEXT('Populator Tools'!$C$25+1,"mm-dd-yy") )
Am I completely off base with this assumption, or on the right track; and if I am on the right track how do I fix this so it works?
Any advice will be helpful.
Code Below.

Thanks in Advance!

The Workbook_SheetChange Code Stored in This Workbook Module

Please Login or Register  to view this content.

Illegal Character Checker/Cleaner - Stored in Module 1

Please Login or Register  to view this content.

**mudraker** · 03-07-2008, 05:01 AM

A change to a formula value by the sheet being recalculated does not trigger a Workbook_SheetChange event macro

It will triggers a Workbook_SheetCalculate or a Worksheet_Calculateevent macro

**ALaws** · 03-07-2008, 01:04 PM

Well, this definatly had an effect, and I think we are on the right track.
I took the code from above, and put it in a Workbook_Sheet Calculate Event Macro. Then, after holding my breath, I changed the value in C25 on the first sheet (the cell driving the formulas in C7 on all the others) and ....
I got an error message:

Run-time error '424':

object required

The line highlighted in the VBA window (debuger) was:

Please Login or Register  to view this content.

Unfortuanatly because this is code written by someone else, I don't exactly know how to go about fixing this. Any thoughts?

Thanks for everything so far, and everything that may come.

Amber

Originally Posted by mudraker

A change to a formula value by the sheet being recalculated does not trigger a Workbook_SheetChange event macro

It will triggers a Workbook_SheetCalculate or a Worksheet_Calculateevent macro

**ALaws** · 03-07-2008, 05:37 PM

Just in case it would help anyone grasp what I am trying to accomplish, I thought I should include a copy of the workbook.
Please note:
I have changed back the code from the previous post due to the error message. I didn't want to lock up anyone elses computer. I only got out of it by Ctrl+Alt+Delete.

Thanks again to anyone who is looking into the problem.
Amber

**Leith Ross** · 03-07-2008, 07:47 PM

Hello Amber,

I add the following macro to your workbook which is attached. With this macro you no longer need the user to press CTRL+Shift+G to update the worksheet names. Once the date is entered into C25 on the Populator Tools sheet, all the other sheets' names are changed.

Populator Tools event macro

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**ALaws** · 03-10-2008, 12:01 PM

Thank you soooooooo much Leith, your solution was both simple and beautiful!

Great Job!

I would also like to say that am so glad I found the forum some years back. Every time I have had one of these big issues you all have been there to help me gather the pieces and make it work.

To all of you who have helped through the years...

Thank You!