disable renaming tabs in Excel 2

[mmaginniss]

I would like to disable the ability for users to rename the tabs at the bottom of an Excel page. Any ideas on how to do that?

 

[ssVBAdev]

Hide Them.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[mmaginniss]

That's one way to do it... but I would really like the user to be able to see the tabs and navigate with them. I am creating timesheets that have dates in the tabs. I want the users to be able to navigate between time periods to update their timesheets, but I don't want them to be able to rename the tabs because then it screws up the master file I have linked to all these timesheets. Thanks!

 

[ssVBAdev]

Protect the Workbook Structure then (Tools-Protection-Protect Workbook). That way the users can see the tabs but cannot change them. Type in a password also if you really don't trust 'em ;-).

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

OK If you really need to see the sheet tabs and don't want to hide them, here's a way using code.

First the MakeList procedure makes a very hidden sheet that contains all the sheet names ALONG WITH each Sheet's Code Name.

Second, the Workbook_SheetDeactivate event looks up the Code Name and renames the sheet with the stored name...

Public bChange As Boolean
Sub MakeList()
    Dim wsList As Worksheet, lRow As Long
    bChange = True
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    On Error Resume Next
    With Worksheets("MyVeryHiddenList")
        .Visible = xlSheetVisible
        .Delete
    End With
    Worksheets.Add
    Set wsList = ActiveSheet
    With wsList
        .Name = "MyVeryHiddenList"
        .Cells(1, 1).Value = "SheetNames"
        .Cells(1, 2).Value = "CodeNames"
        lRow = 2
        For Each ws In ActiveWorkbook.Worksheets
           If ws.Name <> .Name Then
              .Cells(lRow, 1).Value = ws.Name
              .Cells(lRow, 2).Value = ws.CodeName
              lRow = lRow + 1
           End If
        Next
        .Cells(1, 1).CurrentRegion.CreateNames _
            Top:=True, _
            Left:=False, _
            Bottom:=False, _
            Right:=False
        .Visible = xlSheetVeryHidden
    End With
    bChange = False
End Sub


Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If bChange Then Exit Sub
    ptr = Application.Match(Sh.CodeName, Worksheets(&quot;MyVeryHiddenList&quot;).Range(&quot;CodeNames&quot;), 0)
    If Not IsError(ptr) Then _
        Sh.Name = Application.Index(Range(&quot;SheetNames&quot;), ptr, 1)
End Sub

Just another option

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ssVBAdev]

Skip:
Nice Job!!

But I would go one step further an add:


Private Sub Workbook_NewSheet(ByVal Sh As Object)
If bChange Then Exit Sub
MakeList
Application.ScreenUpdating = True
End Sub


This way, each time a new sheet is added, the MakeList procedure runs updating the Worksheet List.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

Except that you want to have a chance to rename the sheet BEFORE it is added to the list ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ssVBAdev]

Umm... ...I knew that

Isn't it Friday yet???

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[mmaginniss]

You guys are awesome. Thanks.

 

[ssVBAdev]

I'll give you a star for that one.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

What is it that THEY say...

"A constantly observed daily time-reconing device..."

or sumthin' like that???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com