Event for / or trapping a Sheet RENAME

[tmktech]

Hello Tippers!

Is there a way to capture when a sheet is RENAMED? I've got routines that maintain dynamic sheet lists based on adds & deletes, but the RENAME has got me stumped.

Thanks in advance.

TMKTECH

 

[ETID]

....You can prevent users from renaming sheets.

Is this an option?

Tools>Protection>Protect workbook ...select structure.

 

[MarkOwen]

Could you not use the Worksheet_Activate() event to store the name of the sheet into a public variable and then use the Worksheet_Deactivate() event to check whether the name has changed and if so run further code to update your list i.e.

Public strName as string

Private Sub Worksheet_Activate()
strName = Activesheet.Name
End Sub

Private Sub Worksheet_Deactivate()
If Activesheet.Name <> strName Then
'Run code to update list
End If
End Sub

HTH

 

[tmktech]

ETID: Unfortunately that's not an option.

MARK: Yeah, that's probably the best way. And by the way, I discovered there were several other threads addressing this same issue. Shoulda done my search first.

Thanks for the replies.

TMKTECH

 

[xlbo]

Or - you could use dynamic named ranges that don't require any maintenance
have a look here:

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

for more info

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tmktech]

Thanks, Geoff.

That's a great tip that I can use (I've been stringing together the range portion of the REFERS TO of the NAMES.ADD to change the range).

But, I'm not sure how that's help me keep my list of sheets up to date when sheets are DELETED? Am I missing something.

FYI - My app have several protected sheets (proprietary) and several unprotected user-defined sheets (I lock and unlock the workbook as the user moves around). I need to keep track of the user-defined sheets to drive some in-cell dropdown lists.

TMKTECH

 

[xlbo]

Apologies - I misread the question - I thought it was about maintaining range names not sheets as sheet deletions etc can mess up range names

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tmktech]

No schweat, Geoff.

I appreciate the useful tip. As long as I've been in Tek-tips (couple years) you've always been gracious in sharing your time and knowledge.

FYI - I'm about to post a new ? regarding forms / lists / combo boxes. I'd love to get your feedback.

All the best!

TMKTECH