Event rename worksheet

  • Thread starter Thread starter ojv
  • Start date Start date
O

ojv

Are there any way of trapping the renaming of a sheet, i.e. the name occuring
on the tab, in an application level event Excel 2003?

Any help appreciated.

ojv
 
There is no sheet rename event but you could check sheet names in some other
event like Before_Save.

Btw, if you're concerned that users changing sheet names would destroy your
code, you should use the sheets' codenames in code rather than their Excel
names.

--
Jim
| Are there any way of trapping the renaming of a sheet, i.e. the name
occuring
| on the tab, in an application level event Excel 2003?
|
| Any help appreciated.
|
| ojv
 
thx for responding. The problem i have is that i use a commandbarcombobox on
a menubar to list sheetnames in activeworkbook (for books with large number
of sheets). If user change sheetname i would like to update the list for the
relevant combobox. Any way to handle that?
 
That's a tough one. There is no event when a commandbar combobox is clicked
to open it so you can't use that to update the list.

Options I can think of-
-Use the Thisworkbook Workbook_SheetSelectionChange event to check your list
of sheet names versus the real names and update the combobox when they are
different. This wouldn't add much overhead and wouldn't guarantee the list
was up to date but it probably would be most of the time.
-Make the combobox a button instead and have it open something you can
update dynamically like a userform.
-Live with the names in the combo box being wrong potentially but update the
list whenever the user picks a name in the combobox.

Btw, is not the user deleting/adding/re-ordering sheets also a problem?

--
Jim
| thx for responding. The problem i have is that i use a commandbarcombobox
on
| a menubar to list sheetnames in activeworkbook (for books with large
number
| of sheets). If user change sheetname i would like to update the list for
the
| relevant combobox. Any way to handle that?
|
| "ojv" wrote:
|
| > Are there any way of trapping the renaming of a sheet, i.e. the name
occuring
| > on the tab, in an application level event Excel 2003?
| >
| > Any help appreciated.
| >
| > ojv
 
Again thx for responding - not the least the speed of it. The options you
mention i have thought of, but as you say wouldn't guarantee an uptodate
list. Deleting/adding sheets is no problem as i can trap it in application
level events, reordering would be the same problem, but is no issue for me as
i sort the list alphabetically and only use it for activating sheets, not
having to rely solely on the scrollbar for sheet tabs.
 
Back
Top