protect unwanted changes but still enable macros

[sr7]

Hi all,

I have a workbook with a worksheet for each employee to enter their productivity data. There are macros on each sheet to record timestamps and remove completed rows from the table. There is also a dashboard that uses a marco to query all the tables into a combined sheet and then populate various pivot tables into the dashboard.

I would like to protect the sheets from unwanted changes so that the employees only enter data, but it seems like this makes the macros unusable. Any ideas?

Thanks

 

[jasonb75]

You can either have your code unlock the sheet while it runs then lock it again when it's finished, or use a workbook open event to allow vba unrestricted access to protected sheets.

See post 3 in the link below

Allow VBA to modify cells in protected sheet

I wrote a workbook and want protect the sheets from "people edit", but still allow vba code to update cell values and sort ranges, etc. Is this possible?

www.mrexcel.com

 

[sr7]

Thanks for the help!

Are you able to explain this part of the post a bit?
"
I prefer the 1st method because the userinterfaceonly option is NOT remembered upon save/close/reopen.
You have to reset that option each time the book is opened."

Trying to determine which is the best method for our needs

 

[jasonb75]

What they're saying is that it is possible to run your code that needs access to the locked cells while they are still locked if you forget to run the userinterface code line first. The command is only active while the workbook is open, once it is closed the command resets and you need to run it again next time you open it.

Personally, I use a workbook open event to unlock the necessary sheet, then lock it again with userinterfaceonly = true in the same procedure. You could also use worksheet activate, but I think that it can fail if the sheet that it needs to work on is already active when the workbook is opened.

Also, with the first option it is set to each individual procedure (macro) so effectively you could allow one to run on a locked sheet but not another if needed to. Effectively preventing users who don't know the password to unlock the sheet from running certain macros.