Clear macro with protected worksheet not working - suggestions?

shebogen

New member
Joined
Dec 30, 2013
Messages
6
Reaction score
0
Points
0
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.
 

Attachments

  • tax calculator.jpg
    tax calculator.jpg
    97.4 KB · Views: 16

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

New member
Joined
Dec 30, 2013
Messages
6
Reaction score
0
Points
0
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.
 

Attachments

  • tax calculator.jpg
    tax calculator.jpg
    97.4 KB · Views: 11

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

New member
Joined
Dec 30, 2013
Messages
6
Reaction score
0
Points
0
Here is the calculator

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

Attachments

  • Draft Calculator.xlsm
    31.2 KB · Views: 8

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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

New member
Joined
Dec 30, 2013
Messages
6
Reaction score
0
Points
0
only have done macro with record, not module

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.


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

New member
Joined
Dec 30, 2013
Messages
6
Reaction score
0
Points
0
I figured out the macro in module - NEVER MIND. Thank you.

Thanks.

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.
 
Top