Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Protect / Unprotect (Excel XP)

    Is it possible to - Attach code to a macro button that will protect a worksheet and then unprotect a protected worksheet. Toggled action! - And if so, is it further possible to have the text on the button face change to Protect or UnProtect whichever it is set up for at the moment? The first part of the question is very important, the second part is simply cosmetic and not all that necessary (unless it's easy)!.

    I could probably look around and find a way to do this with two separate buttons (maybe even use the macro recorder) but I'd really like to do it with just one button.

    Note - The button only needs to apply to one worksheet and doesn't require a password.
    - Ricky

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protect / Unprotect (Excel XP)

    It's all possible and easy.
    1) Use the View | Toolbars | Control Toolbox to display the control toolbox.
    2) Use the toolbox to draw a button on your worksheet.
    3) Press the Properties button in the toolbox and change the button name to cmdProtect.
    4) Double-click on the command button to go to the Worksheet code pane.
    5) Copy and paste the code below.
    6) Return to Excel and press the Exit design mode in the toolbox.
    7) Close the properties window and the control toolbox.
    8) Press the button and see if it works. HTH --Sam
    <pre>Option Explicit
    Private Sub cmdProtect_Click()
    If Me.ProtectContents Then
    Me.Unprotect
    cmdProtect.Caption = "Protect"
    Else
    Me.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    cmdProtect.Caption = "Unprotect"
    End If
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect / Unprotect (Excel XP)

    NOTE TO USERS of XL97:
    If you want to do this, it is ESSENTIAL that when you are in the properties window, in addtion to changing the name, you should change the "TakeFocusOnClick" property to FALSE.

    Excel97 will NOT allow the worksheet to be protected if an item from the "control toolbox" has the focus. It is a "runtime error, method protect of object worksheet failed"

    This is allowed in later versions of excel.

    Steve

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protect / Unprotect (Excel XP)

    Good information, Steve! I never noticed the TakeFocusOnClick property, but I bet that would have fixed a lot of "weird" problems that I had with XL97. I notice that it is still set to True by default in 2000 and 2002. Setting it False gets rid of the anoyance of having to click back on your worksheet whenever you press the button! I love it! Can you think of any reasons that you might not want to change this property to False? I think that I will always set it to False as standard operating procedure. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect / Unprotect (Excel XP)

    The only reason I can think of for having it keep the focus is if you wanted some code linked to the lose focus event. Other than that I can't thinnk of any.

    One thing to be aware of is that NOT all the items in control toolbox have a set focus property (eg spinners) and they get the focus on pressing, which can cause problems if you use protect/unprotect that is called with this pressed.

    Steve

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Protect / Unprotect (Excel XP)

    Thanks Sam & Steve,

    It did cause a wierd behavior when the selector "locked" onto the button. I figured out how to change the Focus setting. Also, I was able to change the appearance and wording on the button according to what state it is currently in. Example: Warning! The sheet is Unprotected (Red on White). I like it and I appreciate the code and advice.

    Ricky
    - Ricky

Posting Permissions

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