Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: allow macro to get behind cell protection

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My spreadsheet uses Protect Sheet and Protect Workbook. I have protected the range A1.. A3 in my spreadsheet to stop users from messing things up. Instead they have a macro which they can run which will change the formulas in those cells. But as those cells are protected .. the macro errors out when run.

    Is there VB code I can put into the start of the macro that will un-protect those cells, then as it finishes .. puts the protection back on.

    Thanks Bill

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    will protect and

    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False

    will unprotect
    It's never too late to learn something new.

    Ricky

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That works fine, thanks .. but I have a line that is "activesheet.name = xxx" in the macro that renames the worksheet and that line gives an error using this solutiob .. are there any additional parameters I can enter .. or .. add a new line of code ?

    Also, the fix still prompts me for the unprotect password .. which I dont want to be prompted for ( otherwise the users would go and unprotect the whole sheet ) .. can the password be coded into the macro too .. or alternatively .. picked up from the sheet parameters ?

    thanks again

    [ This Message was edited by: billm on 2002-04-18 11:14 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    or is it possible to assign control priveleges to the macro to overcome this protection ?

    Thanks
    Bill

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    ActiveSheet.Unprotect Password:="YourPassword"
    'code here
    ActiveSheet.Protect Password:="YourPassword"

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    As my workbook is protected too .. this line fails unless I manually unprotect the workbook
    ActiveSheet.Name = new_symbol

    is there another instruction that will unlock the workbook and re-lock it again ?

    Also, the following line fails even though the password is the same as I used at the start of the macro to unlock the sheet :
    ActiveSheet.Protect Password:="fred"

    Am I doing anything wrong ?
    Thanks
    Bill

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry,
    Try this as well:

    ActiveWorkbook.Unprotect Password:="Fred"
    ActiveSheet.Unprotect Password:="Fred"

    'your code here

    ActiveSheet.Protect Password:="Fred"
    ActiveWorkbook.Protect Password:="Fred"

    Tom

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    great .. works a treat ..

    Many thanks for your assistance
    Bill

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have just realised that when the macro protects the workbook with the above command, it must be omitting the "windows" option that I normally check when I protect it manually.

    Is there a further option for this command to check both of the options, Structure and Windows ?

    Thanks
    Bill

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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