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?
Rory ArchibaldConnect With a Mentor Commented:
Why is it important to you? If it's for code purposes, you could use the codename instead of the tab name.
[ fanpages ]Connect With a Mentor 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

[ 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 ArchibaldConnect With a Mentor Commented:
Re your last part, that is not possible.
Kelvin4Author Commented:
[ fanpages ]Connect With a Mentor 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.