Excel - run macro on protected worksheet

  Billyke 22:20 25 Apr 2008

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 2008

save as . and then try it

  VoG II 06:30 26 Apr 2008

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 2008

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.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Is this the future of VR and AR?

Best iPad buying guide 2017

Comment regarder le Bureau des L├ęgendes en ligne ?