My macro does not work when I protect worksheet

[tatertot]

Hello All,

I have placed an ActiveX controls button in a worksheet that will perform a macro. I want to lock the worksheet so that users can only access the cells I designate and also run this macro to validate their data input.

My issue:
When I run the macro with worksheet locked, the macro runs. But if other users open the template, the macro will not run with the worksheet protected. How is this fixed?

If you need more details, please let me know! Thanks all for your assistance!!

 

[cerfani]

Unprotect it first...

Sheet.Unprotect("thepassword")

'do your code

Sheet.Protect("thePassword") 'check all args so you can lock it and give users proper functionality

 

[tatertot]

Hi Cerfani,

So would I put "Sheet.Unprotect(thepassword)" after Sub and "Sheet.Protect(thepassword)" after the macro has run?

 

[cerfani]

Correct... something like this...

Sub YourMacro()
    Workbook("workbookname.xlsx").Worksheets(1).Unprotect("password123")

    'put all your code here that edits this worksheet

    'make sure you set all the boolean arguments the way you like or else you use the default values
    Workbook("workbookname.xlsx").Worksheets(1).Protect("password123")
End Sub

 

[tatertot]

Thanks Cerfani! Worked perfectly.