How can i trap the rename event of an excel sheet?

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 wrote:
How can i trap the rename event of an excel sheet?

I don't think you can trap an event to do this.

You could try assinging the Rename shortcut menu item a procedure such
as this:

Sub RenameSheet()
Dim stOld As String
stOld = ActiveSheet.Name
If Application.Dialogs(xlDialogWorkbookName).Show Then
MsgBox "Sheet " & stOld & " has been renamed as " & _
ActiveSheet.Name
End If
End Sub

To assign the procedure to the menu item:
Application.CommandBars.FindControl(ID:=889).OnAct ion = "RenameSheet"

To deassign it
Application.CommandBars.FindControl(ID:=889).OnAct ion = ""



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

See one answer in .programming.

Please don't post the same message to multiple groups. It only tends to
fragment your answers, as well as potentially wasting the time of those
replying to posts that have already been answered.

See http://cpearson.com/excel/newposte.htm for tips on using these
newsgroups effectively.

In article ,
"bmm" newsgroup.com wrote:

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

Hello Bill,

Thanks for this piece of code.
I added the code
Application.CommandBars.FindControl(ID:=889).OnAct ion = "RenameSheet"
in the
Workbook_SheetActivate() event and rest of the code in a MAcro
"Renamesheet"
But it pops up a dialog box everytime we want to rename a sheet, which I
don't want.
Also it won't trap the rename event when user tries to rename the sheet by
double clicking on the sheet name.

Can u suggest me something on this?

thanks & rgds


From: "Bill Manville"
Subject: How can i trap the rename event of an excel sheet?
Date: Tuesday, August 10, 2004 6:01 PM

Bmm wrote:
How can i trap the rename event of an excel sheet?

I don't think you can trap an event to do this.

You could try assinging the Rename shortcut menu item a procedure such
as this:

Sub RenameSheet()
Dim stOld As String
stOld = ActiveSheet.Name
If Application.Dialogs(xlDialogWorkbookName).Show Then
MsgBox "Sheet " & stOld & " has been renamed as " & _
ActiveSheet.Name
End If
End Sub

To assign the procedure to the menu item:
Application.CommandBars.FindControl(ID:=889).OnAct ion = "RenameSheet"

To deassign it
Application.CommandBars.FindControl(ID:=889).OnAct ion = ""



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bmm wrote:
I added the code
Application.CommandBars.FindControl(ID:=889).OnAct ion = "RenameSheet"
in the
Workbook_SheetActivate() event and rest of the code in a MAcro
"Renamesheet"
But it pops up a dialog box everytime we want to rename a sheet, which I
don't want.


You want to do it for one workbook only?

Sub RenameSheet()
Dim stOld As String
stOld = ActiveSheet.Name
If Application.Dialogs(xlDialogWorkbookName).Show Then
If ActiveWorkbook.Name=ThisWorkbook.Name Then
MsgBox "Sheet " & stOld & " has been renamed as " & _
ActiveSheet.Name
Else
' not my sheet
End If
End If
End Sub

Workbook_SheetActivate is not called on opening the workbook, only when you
activate a different sheet within the workbook.

I would put the setting up of the commandbar with the macro in Workbook_Open
and the removal of it in Workbook_BeforeClose.

Also it won't trap the rename event when user tries to rename the sheet by
double clicking on the sheet name.

As I said before, there is no rename event.
The only suggestion I have for this is that changing the name of the sheet
will trigger a calculate event in any sheet that has formulas referencing
cells in that sheet.

You could have a hidden sheet which has a table 2 columns wide and with one
row per (other) sheet in the workbook.
In the first column you put formulas like
=MID(CELL("filename",Sheet3!A1),FIND("]",CELL("filename",Sheet3!A1))+1,255)
which returns "Sheet3"
... one for each sheet.
Then copy the first column and paste special / Values into the second
column.

In this sheet's calculate event, check for row(s) where the first column is
different from the second:

Private Sub Worksheet_Calculate()
Dim iRow As Integer
For iRow = 1 To Me.Range("A1").CurrentRegion.Rows.Count
If Me.Cells(iRow, 1) Me.Cells(iRow, 2) Then
MsgBox "Sheet " & Me.Cells(iRow, 2) & " has been renamed to " &
Me.Cells(iRow, 1)
Me.Cells(iRow, 2).Value = Me.Cells(iRow, 1).Value
End If
Next
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup