Results 1 to 9 of 9

Thread: Clear macro with protected worksheet not working - suggestions?

  1. #1

    Clear macro with protected worksheet not working - suggestions?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	tax calculator.jpg 
Views:	10 
Size:	97.4 KB 
ID:	1932  

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  3. #3
    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	tax calculator.jpg 
Views:	5 
Size:	97.4 KB 
ID:	1936  

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  5. #5

    Here is the calculator

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

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

  7. #7

    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.


    Quote Originally Posted by NoS View Post
    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

  8. #8

    I figured out the macro in module - NEVER MIND. Thank you.

    Thanks.

    Quote Originally Posted by shebogen View Post
    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.

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •