Protecting button

[krishnaa_kumarr88]

Hi all,
Need to protect only the buttons on my sheet, I do not want to protect/lock the sheet I only want to keep users from accidentally changing or moving the button. When I tried protecting the sheet even with all the cells unlock the macro would not work. Thanks
I am using both Form control and ActiveX control in my sheet.
Any idea guys?

[Ken Puls]

If you want to protect your worksheet, but still let your code run, then you can use code like the following to do so:

ActiveSheet.Protect userinterfaceonly:=True

The only issue is that you need to re-run this code every time the workbook launches, as the setting is not persistent between workbook sessions. When I need to do this I'll usually just put it in a Workbook_Open routine.

Hope that helps,

[krishnaa_kumarr88]

Hi ,

thanks for your kind help.

Do i need to put ur code in the General Workbook ?

[Ken Puls]

It depends if you're trying to run it on demand for a specific worksheet, or if you want to do all worksheets in the workbook.

If you want to re-protect all worksheets at open, and make sure userinterfaceonly is set to true as well, then you'd drop this in the ThisWorkbook module:

Private Sub Workbook_Open()
    Dim pWord As String
    Dim ws As Worksheet
    
    'Set your password here
    pWord = "myPassword"
    
    'Re-protect all sheets
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect Password:=pWord, userinterfaceonly:=True
    Next ws
    
End Sub

If you want to do a single worksheet, then you'd use code like this in whatever routine you decide needs it:

Worksheets("Sheet_x").Protect Password:="myPassword", UserInterfaceOnly:=True

Of course you'd need to update the password and the name of the worksheet.

Does that make more sense?