PDA

View Full Version : [SOLVED] Protect worksheet tab name



malik641
08-03-2005, 04:42 AM
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
08-03-2005, 05:29 AM
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
08-03-2005, 08:03 AM
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
08-03-2005, 08:59 AM
You must protect the Workbook for that. (Tools | Protection | Protect Workbook)

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

malik641
08-03-2005, 09:12 AM
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 :thumb ).

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

Zack Barresse
08-03-2005, 09:14 AM
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
08-03-2005, 09:18 AM
Cool, thanks firefytr! :thumb

Zack Barresse
08-03-2005, 11:21 AM
Not a problem! Glad I could be of service.

Take care!

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

Aaron Blood
08-03-2005, 12:28 PM
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
08-03-2005, 02:26 PM
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
08-03-2005, 07:24 PM
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 :thumb.

Aaron Blood
08-04-2005, 05:50 AM
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 :thumb.

M: Cool...

FF: Peace...

You guys got it nailed then.