Protecting a worksheet

[TheShaunMichael]

I have created a worksheet that is controlled entirely by buttons and userforms. How can I protect it so nobody can change the worksheet name and/or alter the cells/rows manually? The userforms will change/delete/insert rows and information though...

 

[VoG]

Try

Sheets("Sheet1").Protect Password:="abc", userinterfaceonly:=True

To prevent the user changing the sheet name you'll need to protect the workbook.

 

[jbeaucaire]

You can set the protection in a ws_activate macro that flags the sheet as "OK" to edit with macros only when it is activated.

1) Right-click on the sheet tab and select VIEW CODE
2) Paste in this activation macro:

Private Sub Worksheet_Activate()
    Me.Protect "password", UserInterfaceOnly:=True
End Sub

 

[TheShaunMichael]

I pasted what you both wrote and I was still able to edit cells so I added "allowformattingcells:=false" and it works great!

Now, do you know if there's a way to be able to select a row but not be able to edit it or anything like that?

Thanks!

 

[jbeaucaire]

1) Turn on the macro recorder
2) Now let the recorder record YOU as you go through the Tools > Protection > Protect Sheet and set the flags the way you want them.
3) Turn off the recorder
4) Examine the code to get the flags you want and add them to your code.

The recorder will not show the UserInterfaceOnly flag nor the password, you'll have to put those in yourself with the syntax we've already shown.