Trap Sheet Name Change

[Otto Moehrbach]

Excel 2002, WinXP
Is there any way to trap the event (fire an event macro) of a user changing
a sheet tab name?
The Worksheet_Change macro doesn't fire with this change.
Thanks for your help. Otto

 

[Frank Kabel]

Hi
try the worksheet_calculate event

 

[Otto Moehrbach]

Frank
That event macro doesn't fire when the sheet name is changed. Thanks
anyway. If you have any other ideas please send them.
My objective is to trap the event and, if necessary, bring the old sheet
name back. This latter part appears to be a major problem because the Undo
is not triggered when the user changes the tab name. Otto

 

[Myrna Larson]

When you open the workbook, you could save all sheet names in an array
(Workboo_Open event). I believe you could use the Calculate event. Every time
the worksheet is calculated, call a routine that checks the sheet names
against your stored list and put them back if necessary.

 

[keepITcool]

This might work...

Code must reside in Thisworkbook's codemodule.
It will check if the name is different from the CODENAME
(the name you see in VBeditor project explorer.)

First change all the Codenames to be equal the sheet name
(note that codenames cannot contain spaces..
if that is a problem you could do a simple REPLACE
or use underscores iso spaces.)

it will trigger very often so keep it simple!


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
If Not TypeOf Sh Is Worksheet Then Exit Sub
If Sh.Name <> Sh.CodeName Then
MsgBox "Sheet renaming NOT allowed"
Sh.Name = Sh.CodeName
End If
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Jake Marx]

Hi Otto,

That event macro doesn't fire when the sheet name is changed. Thanks
anyway. If you have any other ideas please send them.
My objective is to trap the event and, if necessary, bring the old
sheet name back. This latter part appears to be a major problem
because the Undo is not triggered when the user changes the tab name.

If you put the sheetname in a formula reference, changing the sheet name
will trigger the calculate event. For example, in at least one formula on
the sheet, enter "=OldSheet!A1" instead of just the local address. Then,
when you rename the sheet to NewSheet, the calculate event will fire.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jake Marx]

Hi Otto,

Excel 2002, WinXP
Is there any way to trap the event (fire an event macro) of a user
changing a sheet tab name?
The Worksheet_Change macro doesn't fire with this change.
Thanks for your help.

I didn't mention this in the other post, but why are you restricting the
renaming of worksheets? If it's to avoid breaking existing VBA code, you
could change your VBA code to use the worksheets' codenames instead of the
worksheet name from Excel. For more info on codenames, see
http://www.cpearson.com/excel/codemods.htm.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]