Locking Tabs within a Workbook that has macros - Novice VBA

[Zaytch]

Hi there! I am trying to lock tabs in a workbook that has links to other spreadsheets and macros. I found help at the following link:

How to prevent users changing the Worksheet Name with VBA | Dedicated Excel

However when I use the code given:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveSheet.Name <> "Data" Then
ActiveSheet.Name = "Data"
End If
End Sub

I get the following error:

Compile error: Expected: Then or GoTo

I am too novice to know what this means or how to fix it. Can you 1) help me fix it, or give me another example of a code that will help me lock down my Spreadsheet names?

Thanks!

 

[tygrrboi]

The above code works for me...

However I think I would prefer to either make it a Workbook_SheetSelectionChange event (that way it will reset the name even if you click on a different sheet...)

In the Workbook code area put...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets([COLOR=#ff0000]1[/COLOR]).Name <> "Data" Then
    Sheets([COLOR=#ff0000]1[/COLOR]).Name = "Data"
End If
End Sub

where 1 is the number of the sheet you would like to keep as "Data" (you can see what number the sheet is for yourself in the project browser.)


...or just password protect your workbook similarly to how you'd password protect a sheet.