A vba solution to prevent a user changing worksheet.name by overtyping name on sheet tab

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
                   page.name =page.nameU
       end if
    End Sub

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Why is it important to you? If it's for code purposes, you could use the codename instead of the tab name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
As rorya described, you could use the CodeName property of each worksheet you wish to interact with programmatically.

In fact, taking your idea a little further, within the Activate event of the respective Worksheets you could rename each back to the name you wish to use, or even handle all Worksheets within the same routine at the Workbook level; Workbook_SheetActivate(), based on their individual CodeName value.
Kelvin4Author Commented:
Sounds like Excel worksheet.CodeName is equivalent of Visio Page.NameU
Will test later this pm and return, thanks to both

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

[ fanpages ]IT Services ConsultantCommented:

I have successfully written some code (to detect a name change for a set of worksheets, in order to rename them to the original names) that causes MS-Excel 2010 to crash on each & every occasion I use it!

I will not post this code now until I have been able to identify the issue.  However, I will continue to work on it.

I hope the ability to reinstate a worksheet's name is possible, but changes to some properties of a worksheet are restricted from within event procedure code; so this may be the case here.
Kelvin4Author Commented:
Thanks, both
- I attach an xl file that checks the sheets names.

1. at Workbook_Open event, sheets are named (demo names only) and names are saved to public array (string)
2. at sheet_activate / _deactivate events, current sheet names are compared with names in array. If there is a difference the array version is used to correct the sheet.name.
3. The events in #2 are under control of a boolean var so that calls from these events are prevented when the visibility of sheets is being manipulated via macros.

I'd be glad for criticism of my code (late learner!)

In response to rorya: I took the point, and .CodeName is new to me. I accept that code.name would insulate vba proceedures from user changes in sheet name.
But this Excel-plus-visio piece is complex and I want to control the user as much as possible.
I dont want users confusing each other by changing the names of these xl data-driven sheets.

Bye the bye: anyone know how to inactivate (via vba)  the 'Insert worksheet' tab that enables users to add new sheets .. from the right hand end of the tab-line?

Subject to your comments/ strictures/ any other suggestions, I'm ready to sign off this Q ..
Rory ArchibaldCommented:
Re your last part, that is not possible.
Kelvin4Author Commented:
[ fanpages ]IT Services ConsultantCommented:

Bye the bye: anyone know how to inactivate (via vba)  the 'Insert worksheet' tab that enables users to add new sheets .. from the right hand end of the tab-line?

You could add something like this to the "ThisWorkbook" code module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

  Application.DisplayAlerts = False
  Application.DisplayAlerts = True
End Sub

Open in new window

The action of adding a new worksheet can still be undertaken, but the newly created worksheet will then be removed again.

You could, of course, add a MessageBox to advise the user accordingly.


Kelvin4Author Commented:
rorya & fanpages: Thank you, I'm going forward again.

Bonus points for rorya on this occasion, as first responder with a key question and worksheet.codeName solution.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.