Protect the name of a tab

[Roccofan]

How can I prevent users from changing the names of tabs in my workbook?

RF

 

[Hero-0952]

Hello Roccofan.
The quick way to do this is to go to Tools / Protection / Workbook. You may use a password or just click OK without using a password. This will take away the right-click option and the Format / Rename options to rename the worksheet tab.

Regards,

ViperGTS

 

[stevebausch]

Investigate the Tools-Protection options.

I can't recall if protecting the worksheet will protect the sheet tab names, or if protecting the workbook will protect the sheet tab names.

 

[GeorgeB]

Write some code in the Workbook before close event that will cycle through the sheets. Then in the sheet activate events check the sheet name and change it back if it has been changed.

 

[Roccofan]

Thanks guys, but I only want to protech the name of the sheet not the cells in the sheet.

George B: If you could provide some sample code, I could take it from there.

Thanks.

RF

 

[stevebausch]

You just need to select the whole sheet, FormatCells-Protection, and uncheck the Locked checkbox.

Then you can have the sheet protected, the sheet tab names locked, but the cells are wide-open for editing.

 

[GeorgeB]

Put this in the workbook before close event

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each sht In Worksheets
sht.Activate
Next
'//Cycle thrugh twice to get al sheets
For Each sht In Worksheets
sht.Activate
Next
End Sub

This in each sheet you want to protect
Just change TEST to the name of the sheet

Private Sub Worksheet_Activate()
If ActiveSheet.Name <> "TEST" Then
MsgBox prompt:="This sheet must be named TEST", _
Title:="NOTICE"
ActiveSheet.Name = "TEST"
End If

End Sub

 

[Mudface]

On 2002-08-28 11:21, Roccofan wrote:
Thanks guys, but I only want to protech the name of the sheet not the cells in the sheet.

George B: If you could provide some sample code, I could take it from there.

Thanks.

RF

As Viper and Steve have said, just protecting the workbook will protect the sheet name, you don't really need code for this as all sheet names will be protected anyway. You can also protect the workbook without protecting individual worksheets if you wish.
This message was edited by Mudface on 2002-08-28 11:56