PDA

View Full Version : PROTECT SHEET NAME



Ash Koparkar
02-27-2019, 08:55 AM
​using vba code How to prevent sheet tab names means user can't change sheet name ?

Jan Karel Pieterse
02-27-2019, 10:28 AM
Protect the workbook (Review tab).

Logit
02-27-2019, 06:09 PM
.
If you don't have a large number of sheets to protect you can place this macro into each SHEET MODULE. Obviously you will
need to change the sheet name in the macro to match the SHEET MODULE the macro is being pasted in.



Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)


If ActiveSheet.Name <> "Master" Then '<--- edit sheet name to match existing name
ActiveSheet.Name = "Master" '<--- edit sheet name to match existing name
End If
End Sub

Paul_Hossler
02-27-2019, 07:19 PM
​using vba code How to prevent sheet tab names means user can't change sheet name ?

If you're worried that users will change the name on the tab of a worksheet and that might cause a macro to break:

Worksheets ("Stores") subscript error because some user changed the name to Worksheets("Dept")

I'd change the code name that the user doesn't see to something and use that in my macros.

Then it doesn't matter is the user changes the name. You can always get it by using "Data.Name" in my example


23814

Ash Koparkar
02-27-2019, 09:02 PM
.
If you don't have a large number of sheets to protect you can place this macro into each SHEET MODULE. Obviously you will
need to change the sheet name in the macro to match the SHEET MODULE the macro is being pasted in.



Private Sub worksheet_SelectionChange(ByVal Target As Excel.Range)


If ActiveSheet.Name <> "Master" Then '<--- edit sheet name to match existing name
ActiveSheet.Name = "Master" '<--- edit sheet name to match existing name
End If
End Sub

Ash Koparkar
02-27-2019, 09:15 PM
:(

Logit
02-27-2019, 09:24 PM
.
Did you paste it into the Sheet Level Module ?

Ash Koparkar
02-27-2019, 11:16 PM
.
Did you paste it into the Sheet Level Module ?

Logit
02-28-2019, 08:42 AM
See attached.