Event triggered when renaming sheet?

**twd000** · 09-29-2008, 06:21 PM

I need to declare an event that happens when a worksheet is renamed

the code that would trigger the event is

ActiveSheet.Name = NewName

it looks like VBA does not have any such thing built-in

are there any other options?

**shg** · 09-29-2008, 06:23 PM

If your code is renaming the sheet, why not just call whatever procedure you want to execute when you do it?

**twd000** · 09-29-2008, 06:36 PM

actually it's not my code that's renaming the sheet - it's code from a commercial XLA add-in that I'm interacting with. I need to recognize when the Add-In renames a sheet and execute my code when that happens

**shg** · 09-29-2008, 06:53 PM

There is indeed no such event.

Depending on why you need to know that the sheet is renamed, there may be alternatives.

**twd000** · 09-29-2008, 06:55 PM

I need to know that the sheet has been renamed because it tells me what type of data has been input to the sheet. It also tells me that the Add-In is finished writing data to the sheet because renaming is the last action it performs before finishing the subroutine.

**shg** · 09-29-2008, 06:58 PM

So check the name of the sheet before you do anything that depends on its contents ...

And you know the add-in is done when your code execution resumes, because VBA is single-threaded.

**twd000** · 09-29-2008, 07:02 PM

the problem is that I don't want to start executing my code until I'm sure that the add-in has finished.

Once my code has started, I can check the name of the sheet to determine what to do with it, but I can't start running my code until I'm sure that no more data will be written to that sheet

hope that makes sense

**shg** · 09-29-2008, 07:05 PM

What triggers the add-in?

**twd000** · 09-29-2008, 07:08 PM

the user triggers the addin

it has a custom menu in the excel toolbar. by clicking through the menus and some dialog boxes, the user defines what type of Designed Experiment should be populated on the new sheet. Then the add-in creates that DOE experiment and populates the data onto the new sheet. finally the sheet is renamed at which point I want my code triggered to copy the data from the new sheet into various locations in other sheets

**shg** · 09-29-2008, 07:19 PM

You could shadow the button click event, and then start an OnTime procedure that checks the name of the sheet periodically (the first invocation may not run until the add-in is complete).

Or give the user another button to trigger your code.

Sorry, I don't have a better idea.

**twd000** · 09-29-2008, 07:56 PM

is the click event you refer to associated only with UserForms?

the addin doesn't use a UserForm, but instead some type of dialog box

does this click event work for these dialog boxes too?

**shg** · 09-29-2008, 08:18 PM

If you get the ID of a button (the add-in's button, in this case), you can process then event in your code as well -- I think . I don't do a lot of work with menus -- Andy Pope, RoyUK, or some others could do this in their sleep.

Given the number of exchanges in this thread, I'd suggest you start a fresh one, something like "Shadow click event for menu button"