My excel sheets are named programmatically and the user must not change them.
Worksheet tabs must be visible to user.
The user has no access to workbook vba.
How do I prevent user changing worksheet.name by over-typing the name of the sheet tab?
in VISIO, my solution (which is suspended whilst the pages are 'officially' named via vba):
Private Sub Document_PageChanged(ByVal Page As IVPage)
if page.nameU <> page.name then
However in EXCEL:
- 1: Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
does not detect a change in tab name
- 2: .NameU (Universal Name) does not exist in excel.
I've tried working the object model, but need more skill to uncover a clue.
Else, I propose:
- On Workbook_Open, to name each worksheet with an 'official' worksheet.name, which I will store in a scripting runtime Dictionary of as items, each with a key value = the Worksheets.Item collection value for that worksheet.
- Then use the events: Workbook_SheetActivate and Workbook_SheetDeactivate (ByVal Sh As Object) to extract the worksheets collection item value for Sh, and then use that dictionary key to check whether Sh.name = the corresponding item value.
Is there a more direct approach, and is my approach sensible?