Hello - is there an event or a good way to invoke code when a sheet is renamed?

--
Thanks,
Mike

Mike,

There is no event for a worksheet name change.
You could use the Activate and Deactivate sheet events
to check the sheet name.
However that is not a real time catch for a rename.

What you can do is set an object reference to the sheet
and use that to access the sheet.
The user can change the name and it won't matter...

Dim objSheet as Excel.Worksheet

Set objSheet = Worksheets("originalname")
-or-
Set objSheet = ActiveSheet
-or-
Set objSheet = Worksheets(1)
-or-
Set objSheet = Workbooks("SomeOther").Worksheets("sludge")

Use it like this...
objSheet.Range("B5").Value = "Oats"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Mike Archer"
wrote in message
Hello - is there an event or a good way to invoke code when a sheet is renamed?
--
Thanks,
Mike

Hi Mike,

As Jim says no event is directly triggered when a sheet is renamed. What you
could do is get a cell formula to return the sheet name which in turn will
trigger the sheet calculate event if the sheet is renamed

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)

the file must have been saved for the formula to work

If the formula is in say A1, you would need to store the original value of
A1, either in some other cell or as a module level variable (first time in
say the workbook open or activate event).

If, in the calculate event, the value of A1 has changed call your code and
update the stored value. Note if code called in the calculate event goes on
to trigger another calculation you'll need to early exit the code triggered
event(s).

FWIW if you want to return all sheet names you could try this named
formula -

=MID(NOW()*0 & GET.WORKBOOK(1), FIND("]", GET.WORKBOOK(1))+2,31)

Array enter the named formula in a row of cells, as many as there will ever
likely be sheets in the workbook (or transpose if you prefer in a column).
This array entered named formula will also change if sheet is re-ordered,
added or deleted (which might be useful and/or require extra work to cater
for)

Similar to the above, store the array of values for comparison in the
calculate event.

Regards,
Peter T


"Mike Archer" wrote in message
...
Hello - is there an event or a good way to invoke code when a sheet is
renamed?

--
Thanks,
Mike