How do I lock cells containing formulas but still allow macros to work?

Question

I have a worksheet that I make my employees fill out, and I have calculated cells that I want to lock so they cannot change them. I have selected the cells and selected properties and ensured that the "lock" checkbox is checked. When I protected the worksheet/workbook the "export to csv" macro button stopped working. In order to enable the macro to be completed I inserted this VB code into the Workbook:

Private Sub Workbook_Open()

Dim wSheet As Worksheet
For Each wSheet In Worksheets

    wSheet.Protect Password:="password", _
    UserInterFaceOnly:=True

Next wSheet


End Sub

This worked but had the unintended side effect of allowing my locked formulas to be able to be edited even though they were locked. Only cells containing non-formula values remained locked. What is the proper way to allow macros but still lock formula cells?

You might have to .Value = Value on the formula block and reassert the formulas with .Formula = "..." when you want the cells recalculated on new data. The event sub Worksheet_Calculate is considered a 'macro'. — user4039065
@Jeeped What if to change the UserInterFaceOnly at the end of the macro. Please take a look at my answer and let me know if this works. I don't have access to excel right now to test it. — M--
@Masoud - Just off the top of my head, Workbook_Open is private to the ThisWorkbook code sheet. I'm not sure you can call it. — user4039065
@Jeeped You're right. It should be Public. But aside from that, set the UserInterFaceOnly to true would resolve the issue? — M--
@Masoud - Well, the problem is that values are changing on the worksheet that affect the results of formulas and the OP wants the results of the formulas locked. I don't think it can be done without reverting the formulas to their static values and reasserting the formulas when he/she requires a recalculation. — user4039065

Aaron Martin Aaron Martin · Accepted Answer · 2017-04-06T15:56:53

My solution was to lock the entire workbook and worksheets, then code into the VB button the disabling of the lock then the re-enabling the macro. Like so:

Sub MyMacro()

Sheet1.Unprotect Password:="password"

    'insert code here

    Sheet1.Protect Password:="password"

End Sub

I then deleted my Workbook_Open code.

How do I lock cells containing formulas but still allow macros to work?

1 Answers