Trap the rename event of Excel sheet

  • Thread starter Thread starter bmm
  • Start date Start date
B

bmm

Hello,

How can i trap the rename event of an excel sheet?
Is it possible to get the renamed sheet name as soon as it'd changed?

thanks and rgds
 
bmm,

There is no rename event of an Excel sheet. You may be able to use the
calculate event of the sheet, though. In Cell B1, use this formula:

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

And in cell C1, enter the current sheet name. Then use this calculate
event:

Private Sub Worksheet_Calculate()
If Range("B1").Value <> Range("C1").Value Then
Application.EnableEvents = False
MsgBox "The sheet just got renamed from" & Chr(10) & _
Range("C1").Value & " to " & Range("B1").Value & "."
Range("C1").Value = Range("B1").Value
Application.EnableEvents = True
End If
End Sub

This won't work if the workbook hasn't been saved.

HTH,
Bernie
MS Excel MVP
 
bmm said:
How can i trap the rename event of an excel sheet?
Is it possible to get the renamed sheet name as soon as it'd changed?
Click to expand...

There's no such event. However, you can kludge Calculate events to catch
this. If you use any volatile functions in your workbook, e.g., NOW or RAND,
then renaming worksheets will trigger recalc, and firing Calculate and
SheetCalculate event handlers. If you store the initial worksheet name in a
Constant or initialize a static variable with it, you can check the current
worksheet name against the stored value, and take appropriate action when
they differ.
 
Back
Top