Protected Workbook - allow users to change tab name

[WisdomandLaughter]

Good day,
I am using the following code to allow users to unhide sheets in a protected workbook without compromising the structure of the workbook
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o><o></o>
Sub Protect()<o></o>
<o></o>
ActiveWorkbook.Unprotect<o></o>
Application.Run "Update.xls!GotoNextHiddenWS"<o></o>
ActiveWorkbook.Protect Structure:=True, Windows:=False<o></o>
<o></o>
End Sub
<o></o>
Is there a way to also allow users to change the tab names without compromising the structure of the workbook?

Thank you!
Judy

 

[ColinKJ]

If I understand what you are doing correctly.

You could create a Macro activated by a Key combination, which then opens an Input Box in which the Users can type the new name. The Macro could then unprotect, change the name then protect again.

ColinKJ

 

[WisdomandLaughter]

Colin,

Thank you for the direction. That is exactly what I would like to do. If you have a moment and wouldn't mind, would you be able to provide a sample code along those lines?

Judy

 

[WisdomandLaughter]

Colin,

I had another thought that might be less complicated. The name of the sheet would be the same as cell c1. Is it possible to base the worksheet name (on the tab) on whatever value is entered in c1?

Thanks,
Judy

 

[ColinKJ]

Hi Judy,

The first option, an input box activated with Ctrl+q, you need to set which key.

Code:

Sub RenameTab()
'
'
' Macro recorded 10/03/2009 by Colin
'
' Keyboard Shortcut: Ctrl+q
'
x = ActiveSheet.Name
a = InputBox("Enter New Tab Name", "Change Tab Name", x)
If a = "" Then GoTo endd
ActiveWorkbook.Unprotect
On Error Resume Next
ActiveSheet.Name = a
On Error GoTo 0
ActiveWorkbook.Protect Structure:=True, Windows:=False

endd:
End Sub

Code:

Second option:

The Tab name is changed to whatever is entered into C1

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 3 Then
If Cells(1, 3) = "" Then GoTo endd
ActiveWorkbook.Unprotect
On Error Resume Next
ActiveSheet.Name = Cells(1, 3).Text
On Error GoTo 0
ActiveWorkbook.Protect Structure:=True, Windows:=False
End If
endd:
End Sub

Code:

This code needs to be entered on each Sheet.

Regards

ColinKJ

 

[WisdomandLaughter]

You rock. Thank you for your help!

Judy