Allow Users to Edit Certain Cells While Sheet Protected

[klm1988]

Hi there

I have a worksheet (called "Criteria"), where my colleagues are filling out certain cells, then using a Macro to save the data to another worksheet (called "Data", in the same excel file), and re-set the fields within the "Critera" worksheet, ready for the next entry.

Other than about 12 cells in "Criteria", I don't want anyone to be able to change or delete anything within the file. So, I protected all of the worksheets, and specified those 12 cells using the Tools, Protection, Allow Users To Edit Ranges Menu.

When the worksheet is protected, it allows me to edit those 12 cells, however, when I run the Macro (saving the 12 cells into the "Data" worksheet), I get an error message, saying that because the worksheet is protected, the macro cannot execute.
I tried un-protecting the "Data" worksheet, but even then, the macro still fails when it tries to clear the content out of the 12 cells that have been edited, because the rest of the sheet is protected.

Is there any way around this? It's been driving me crazy for 2 days now!

Thanks in advance!

 

[doofusboy]

the macro still fails when it tries to clear the content out of the 12 cells that have been edited

You can add a line to your macro that Unprotects the worksheet prior to clearing out the content of the 12 cells and then add a line that Protects the worksheet again after those contents have been cleared.

 

[klm1988]

That's great, thanks.

The only thing is, when the macro runs and it unprotects the sheet, it'll prompt the other users of the worksheet to enter the password to enable it to complete the macro. There's not really any point in having the protection there if they know the password to take it off.

Is there any other way around that?

Thanks!

 

[doofusboy]

Users won't be prompted for the password. You would take care of that in your macro code.