Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trap Sheet Name Change
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trap Sheet Name Change
Hi
try the worksheet_calculate event -- Regards Frank Kabel Frankfurt, Germany Otto Moehrbach wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trap Sheet Name Change
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 "Frank Kabel" wrote in message ... Hi try the worksheet_calculate event -- Regards Frank Kabel Frankfurt, Germany Otto Moehrbach wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trap Sheet Name Change
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. On Mon, 25 Oct 2004 18:22:20 -0400, "Otto Moehrbach" wrote: 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 "Frank Kabel" wrote in message ... Hi try the worksheet_calculate event -- Regards Frank Kabel Frankfurt, Germany Otto Moehrbach wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trap Sheet Name Change
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 "Otto Moehrbach" wrote: 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 "Frank Kabel" wrote in message ... Hi try the worksheet_calculate event -- Regards Frank Kabel Frankfurt, Germany Otto Moehrbach wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trap Sheet Name Change
Hi Otto,
Otto Moehrbach wrote: try the worksheet_calculate event 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] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trap Sheet Name Change
Hi Otto,
Otto Moehrbach wrote: 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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I trap the change event (Tab Press key) of these controls | Excel Worksheet Functions | |||
how can I trap the change event (Tab Press key) of these controls | Excel Worksheet Functions | |||
How can i trap the rename event of an excel sheet? | Excel Programming | |||
How To Trap Cell Value Change Event so determine XL calc sequence? | Excel Programming | |||
Error trap the renaming of a sheet to an existing one | Excel Programming |