Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locked Workbooks (XP)

    The following will lock a workbook.
    Sub Locked Workbook()
    Dim ws As Worksheet
    For Each ws In This Workbook.Worksheets
    ws.Protect Password:""yankeezulu", Contents:=True
    Next
    End Sub

    Assume that there is 8 sheets in the workbook. What I would like to be able to do is have the workbook open to a splash screen that will have two buttons. One which will allow an end user to unlock the workbook, the other which will then run the code above to lock the book again.

    Any suggestion would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locked Workbooks (XP)

    A macro name cannot contain a space, and you should have Password:= instead of Password:"

    Should the user provide the password to unlock the sheets, or can the password be included in the unlock macro?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked Workbooks (XP)

    Your absolutely correct about my typing errors. The code in my worksheet is properly entered.
    I would like that the user have to enter the password to unlock the workbook. I have put a button on sheet 1 of my workbook. Just having trouble getting the unlock portion.

    Managed to get second button to unlock the sheet, but haven't yet figured out how to get it to ask for password to unlock.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locked Workbooks (XP)

    Does this do what you want?

    Sub UnLockWorkbook()
    Dim ws As Worksheet
    Dim pw As String
    pw = InputBox("Enter the password")
    If pw = "" Then
    MsgBox "You didn't enter a password!", vbExclamation
    Exit Sub
    End If
    On Error GoTo ErrHandler
    For Each ws In This Workbook.Worksheets
    ws.Unprotect Password:=pw
    Next ws
    Exit Sub
    ErrHandler:
    MsgBox "Can't unlock worksheets!", vbExclamation
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked Workbooks (XP)

    Thanks Hans. Tried to add it to my button 2 and this line For Each ws In This Workbook.Worksheets shows in red. I did remove the Sub UnlockWorkbook as I have the line Private Sub CommandButton2_Click

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locked Workbooks (XP)

    It's your own typo from the first post in this thread - don't you recognize it? It should be ThisWorkbook instead of ThisWorkbook.

  7. #7
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked Workbooks (XP)

    Again it is the typo in the p0st not in the actual code.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locked Workbooks (XP)

    But

    For Each ws In ThisWorkbook.Worksheets

    should not cause a problem. If it does, what error message do you get?

  9. #9
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locked Workbooks (XP)

    Hans, I don't know what the problem was. I originally used Copy and Paste to put the code you provided and got errors. I then decided to delete it and type it in. No errors. Thanks for assistance today. I have learned more about VBA which is a goal of mine.

Posting Permissions

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