Protecting button

krishnaa_kumarr88

New member
Joined
Sep 30, 2014
Messages
26
Reaction score
0
Points
0
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?
 
If you want to protect your worksheet, but still let your code run, then you can use code like the following to do so:

Code:
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,
 
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:
Code:
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:
Code:
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?
 
Back
Top