Excel Macros not running with protect sheet

Question

I have

Excel Microsoft Office Professional Plus 2016

Windows 10

I have an excel Table with Macros and want to "Protect Sheet" but when I do this the Macros do NOT work when someone else opens the file in "Read Only". They do work in "Read Only " if the sheet is Not protected.

The file is only used by two people, one at a time.

I unlocked all cells first then locked cells which have formulas and are dependent on results from other cells. These are working great with the sheet protected. My Macros are assigned to buttons (symbols) which sit above the table in rows 1-5. I also unlocked these rows' cells.

Note below, all items but one are selected on the "Protect Sheet" properties box.

Question: is there a way to protect cells with formulas and still have the Macro assigned symbols work??

Thanks so much

Tasha

Answer 1

You might unprotect the sheet in the code at the beginning of the macro and protect it again at the end.

Sub MyMacro()
ActiveSheet.Unprotect Password:="..."
' your code here
' ...
ActiveSheet.Protect Password:="..."
End Sub

Answer 2

Protecting a sheet should not preclude the use of the macros on the sheet. However, I don't believe you can paste to or change any locked cells by using a macro. As Hans says, you can get around that limitation by unprotecting the sheet within the macro and then protecting it again before exiting the macro. Of course that won't work if the macro doesn't run in the first place. The fact that you have the macro buttons positioned on a table may make a difference - which someone else can shed light on, perhaps. Have you tried repositioning the buttons? You must also ensure that the copy of the workbook is macro-enabled. You can look at this article.