Event for Worksheet Name Change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an application that stores information about where data is located in Excel - worksheet name, starting cell, number of rows, number of columns, etc. This information is used to place new data in the same location at a later time. This works fine until the user renames the worksheet and the location no longer exists.

I'd like to know if there is an event that occurs when the worksheet name changes. If so, the application could just update its stored information with the new sheet name.

If there is no event, is there a worksheet ID or something that identifies a worksheet that stays constant even when the worksheet name changes?
 
Hi
you can use the worksheet codename. This name is not changed by
renaming it.
e.g. the following is identical (if the codename for the first sheet is
sheet1)
Worksheets("Sheet1").Range("a1")
Sheet1.Range("a1")

you can get the codename with
MsgBox Worksheets("sheet1").CodeName

for more information look at the VBA help for 'Codename'
 
Drew Lettington said:
I'd like to know if there is an event that occurs when the worksheet name
changes. If so, the application could just update its stored information
with the new sheet name.
Click to expand...
....

You may have to hack this. If you had a cell formula like

=LEFT(CELL("Filename",foobar!A1),INT(RAND()))

which would always evaluate to "", it'd trigger the SheetCalculate event
handler when worksheet foobar was renamed. You'd have to put code into the
event handler to check all worksheet names.
 
Back
Top