Clear macro with protected worksheet not working - suggestions?

**shebogen** · 2013-12-30, 06:58 PM

I created a worksheet and locked the cells with formulas so users could input data, but not alter cells with formulas that show the results of their input. I then recorded a macro to clear all user input cells. However, when I tested the macro after the workbook was protected again, there is an error because of the protected status. As a curious note, all the cells that I am clearing are user input cells so they are not locked.

**NoS** · 2013-12-31, 01:31 AM

They say a picture is worth a thousand words
but.... this picture doesn't say anything about your macro nor what sheet you are working on.

If you haven't sorted this out yet, my guess is that you are on Sheet1 and that the sheet wasn't unprotected before the clearing of things in the macro.
Try something like this

Code:

Sub ClearThings()

With Sheets("Sheet1")
    .Unprotect Password:="mypassword"
    '
    'do your clearing of things here
    '
    .Protect Password:="mypassword"
End With

End Sub

**shebogen** · 2014-01-01, 05:16 PM

I am sorry. You are correct. I am working on the sheet called "Calculator". The black border cells are cells where users input, the blue are where formulas are and results are returned. In the macro I recorded, I cleared all cells that are black. I need to have other people use this but do not want the formulas touched so the sheet must be protected and the formula cells locked. However, when I do this the "Clear" macro results in an error.

**NoS** · 2014-01-01, 07:29 PM

Sorry to be so blunt but if you want help with your macro and/or workbook a picture is useless.

Post a copy of your workbook. If it contains sensitive information, replace that info with something representative of what you are working with.

You can upload your workbook to this site by going Advanced on the reply page and using the paper clip.

**shebogen** · 2014-01-01, 07:46 PM

Thank you. I appreciate bluntness. The password to unlock the sheet is harperlee

**NoS** · 2014-01-01, 09:15 PM

shebogen, this macro should do what you're after. Just copy and paste it into your Module and give it a try.

Code:

Sub MyClear()
With Sheets("Calculator")
    .Unprotect Password:="harperlee"
    .Range("B6:E6,B10:E19,B24:E25,B32:E32").ClearContents
    .Protect Password:="harperlee"
    .Range("B6").Activate
End With
End Sub

**shebogen** · 2014-01-02, 02:16 AM

Thank you. I have never done a macro except by recording. I am not sure how to put this code in a module. I am not looking for you to cater to a less knowledgeable excel person, just point me in the right direction.

Originally Posted by NoS

shebogen, this macro should do what you're after. Just copy and paste it into your Module and give it a try.

Code:

Sub MyClear()
With Sheets("Calculator")
    .Unprotect Password:="harperlee"
    .Range("B6:E6,B10:E19,B24:E25,B32:E32").ClearContents
    .Protect Password:="harperlee"
    .Range("B6").Activate
End With
End Sub

**shebogen** · 2014-01-02, 02:58 AM

Thanks.

Originally Posted by shebogen

Thank you. I have never done a macro except by recording. I am not sure how to put this code in a module. I am not looking for you to cater to a less knowledgeable excel person, just point me in the right direction.

**NoS** · 2014-01-02, 03:47 AM

No problem shebogen, we all had to start some where.
Sorry I wasn't able to respond right away.
Here is a link that I would have suggested http://www.contextures.com/xlvba01.html