Excel - run macro on protected worksheet

  Billyke 22:20 25 Apr 08

I do the accounts for a charity using their own forms.
There is an Accounts Sheet where all entries are made and given a Transaction Code (TC). From there the data is picked up on other forms by using Lookup and Sum formulas.
I also have a macro that runs an AutoFilter to extract certain TC codes and Copy to Another Location. This data is then copied to another worksheet.
This has worked fine for years but I am now training someone else to do the acounts, so I have locked all cells containing formula and protected the worksheets so that he cannot corrupt anything.

Now the problem:
when the macro is run it works fine, but always throws up an error message if the worksheet is protected.
Runtime error '1004':
"The cell or chart you are trying to change is protected and therefore read-only."

I have tried to run the macro using a keyboard shortcut, adding a button to the toolbar, and adding a button to the worksheet - all with the same result.

How can I prevent the error message appearing?

  rossgolf 23:04 25 Apr 08

save as . and then try it

  VoG II 06:30 26 Apr 08

You need to unprotect the sheet before running the code, like this:

Sheets("Sheet1").Unprotect Password:="xyz"
'your code here
Sheets("Sheet1").Protect Password:="xyz"

  Billyke 20:15 26 Apr 08

Hadn't thought of that approach. Thanks VoG it worked fine.

Haven't done any programming since I had my Oric. Guess it's time I tried to learn again.

This thread is now locked and can not be replied to.

AMD Ryzen release date, specifications and features: Three CPUs from the Ryzen 7 range now…

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…