Protect worksheet tab name

[malik641]

How would I do that?? I want to add it to my existing workbook_open code so I can protect the worksheet tab name:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Worksheets("Immuno-Assay")
    If Not .AutoFilterMode Then
        .Range("A1").AutoFilter
    End If
    .EnableAutoFilter = True
    .Protect Password:="", _
    contents:=True, userinterfaceonly:=True
    End With
    With Worksheets("Freezer Diagrams")
        .Protect Password:="", _
        contents:=True, userinterfaceonly:=True
    End With
    With Worksheets("ReadMe")
        .Protect Password:="", _
        contents:=True, userinterfaceonly:=True
    End With
End Sub

How do I go about this one?

[Justinlabenne]

Put your sheet name that you want in the the Activesheet.Name line...in each worksheet code module,

Option Explicit 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
On Error Resume Next 
ActiveSheet.Name = "YourSheetNameHere" 
End Sub

or have the sheet names reset to the names you want on Workbook_Open or Before_Close events,

or protect the workbook if possible,

or if it's a matter of having the sheet names be correct for usage in code, switch to using the sheets' code names..

[malik641]

I need it to be so that the user cannot right-click and select "Rename" on the tab.

Otherwise what are the sheets' code names??? How would I use Sheet1 in a formula & Script if the tab name is "Bio-Analytical"??

[Zack Barresse]

You must protect the Workbook for that. (Tools | Protection | Protect Workbook)

And what do you mean by 'bio-analytical'?

[malik641]

And what do you mean by 'bio-analytical'?

That's what the worksheet is called. It's the department I work for (even though I really work for every department that needs my help ).

Okay, I think the workbook protect will work fine, but how do I put that into my code???

[Zack Barresse]

Have you tried recording a macro to get your code?

Here is the basics:

Option Explicit

Sub WorkbookProtection()
    ActiveWorkbook.Protect "password", Structure:=True, Windows:=True
    ActiveWorkbook.Unprotect "password"
End Sub

The additional syntax's are not needed, but advisable.

[malik641]

Cool, thanks firefytr!

[Zack Barresse]

Not a problem! Glad I could be of service.

Take care!

P.s. Don't forget to mark your thread Solved!

[Aaron Blood]

I need it to be so that the user cannot right-click and select "Rename" on the tab.

Otherwise what are the sheets' code names??? How would I use Sheet1 in a formula & Script if the tab name is "Bio-Analytical"??

You're wasting time... and effort.

Let the user call the Sheet1 object whatever they want. In your code, refer to the sheet by its VBA object name and the problem is forever resolved.

For instance...

Sheet1 tab name = "Bio-Analytical"

In your code, change this:

Sheets("Bio-Analystical").Select

to this:

Sheet1.Select

Doesn't matter what tab name they ascribe to the Sheet1 object, your code still works!

...at least you're aware of the userinterface only property, that's good!

[Zack Barresse]

I don't know Aaron. While that is a good idea and works very well, I think it's using code when you could be using a native feature of Excel. This alone makes me think that you'd be (albeit probably small) using more overhead with code than the native feature.

Sure no workbook is secure in a password, Excel is not a secure environment. I'm of the thought that you do what you can in the most efficient way with as little overhead as possible; and also with less areas to troubleshoot. MHO.

[malik641]

You're wasting time... and effort.

Let the user call the Sheet1 object whatever they want. In your code, refer to the sheet by its VBA object name and the problem is forever resolved.

I need those names for the Array formulas that are in the worksheet cells themselves. I just protected the workbook and it works just the way I wanted .

[Aaron Blood]

I need those names for the Array formulas that are in the worksheet cells themselves. I just protected the workbook and it works just the way I wanted .

M: Cool...

FF: Peace...

You guys got it nailed then.