Is it possible to lock/protect sheet tab names in Excel 2000 ? 3

[vamoose]

Just wondering. I went to alot of effort to lock, hide and protect my sheet data and formulas, but the sheet tab names are still vulneralbe to but edited. Any ideas, Thanks

 

[SkipVought]

Hi,

Nope!

But what's the problem?

Do you have code that references Sheet Name properties?

Use the CodeName property instead.

Then the users can change tab names to their hearts' desires.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[macropod]

Hi vamoose,

Since you're working with protected sheets, one way you could achieve this would be to use an event-driven macro to force the sheet name to match the value of a designated protected cell. For example, the following code, attached to the relevant worksheet object, will reset its name to that of the text in A1 every time a calculation occurs:

Private Sub Worksheet_Calculate()
With ActiveSheet
Application.EnableEvents = False
.Name = .Range("A1").Text
Application.EnableEvents = True
End With
End Sub

You could use similar code attached to the Workbook object, as in:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim oSheet
Application.EnableEvents = False
For Each oSheet In ThisWorkbook.Sheets
With oSheet
.Name = .Range("A1").Text
End With
Next
Application.EnableEvents = True
End Sub

Note: Neither sub has does any validation/error checking on the contents of A1. You'll need to set your sheets up before adding the code, otherwise the subs will crash.

The first sub would have to be attached to every sheet you'd want to protect but, unlike the second sub, allows the user to add a sheet of their own without having to worry about whether they've got a valid name in your designated cell.

Cheers

Skip: If you've ever had a user rename a sheet whose name is used in external links, you'd appreciate the importance of keeping the sheet name unchanged.

 

[SkipVought]

macropod,

Good tip.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}