Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi everyone,

    I have a piece of code here that will protect the entire workbook (loops to protect all sheets). How do I change the existing code to include a password?

    Sub protectAll()
    Dim myCount 'This line of code is optional
    Dim i 'This line of code is optional
    myCount = Application.Sheets.Count
    Sheets(1).Select 'This line of code selects the 1st sheet
    For i = 1 To myCount
    ActiveSheet.Protect
    If i = myCount Then
    End
    End If
    ActiveSheet.Next.Select
    Next i
    End Sub

    Thank you,
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    ActiveSheet.Protect Password:="hello"
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='teachesms' post='780534' date='18-Jun-2009 15:26']Hi everyone,

    I have a piece of code here that will protect the entire workbook (loops to protect all sheets). How do I change the existing code to include a password?

    Sub protectAll()
    Dim myCount 'This line of code is optional
    Dim i 'This line of code is optional
    myCount = Application.Sheets.Count
    Sheets(1).Select 'This line of code selects the 1st sheet
    For i = 1 To myCount
    ActiveSheet.Protect
    If i = myCount Then
    End
    End If
    ActiveSheet.Next.Select
    Next i
    End Sub

    Thank you,
    Nannette[/quote]

    Did not realize this was already discussed another post. I tried the answer WebGenii suggested:
    ActiveSheet.Protect Password:="hello"
    but doesn't work because when I click the Unprotect sheet button In Excel 2007 it doesn't prompt me for a password...i need a prompt

    I was looking at the code in the other conversation on the board: (but it doesn't prompt either and it sure looks like it should ... when unprotecting:

    Option Explicit
    Sub ProtectSheetsSamePassword()
    Dim sht As Variant
    For Each sht In ActiveWorkbook.Sheets
    sht.Protect "common"
    Next
    End Sub

    Sub UnProtectSheetsSamePassword()
    Dim sht As Variant
    For Each sht In ActiveWorkbook.Sheets
    sht.Unprotect "common"
    Next
    End Sub

    Although it asks me for the password "common" when I go and click on unprotect the sheets... it doesn't prompt me when i run the unprotect macro from the macro window.

    Would I have to protect that particular code by going to the project explorer, pointing at the project and right clicking on it. Selecting VBA project properties, clicking on the protection tab and checking the Lock project for viewing and key your password. Would this keep them out? Going to go try...half the time I get messed up and solve it myself... Maybe that's it...let me go try

    EDITED RESPONSE:

    1. Yes that protects the Project so no one can get in and see the password in the code of "common"
    2. It still allows anyone to go to ...Review...Macros...Run UnprotectSheetsSamePassword() and it does not prompt for the password "common".

    Any help on # 2 would be GREAT
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Code:
    Sub UnprotectPromptPassword()
      Dim strPassword As String
      Dim sht As Variant
      strPassword = InputBox("Enter the password to unlock the sheets")
      On Error GoTo ErrHandler
      For Each sht In ActiveWorkbook.Sheets
    	sht.Unprotect strPassword
      Next sht
      Exit Sub
    ErrHandler:
      MsgBox "Mwahahaha, you stupid fool!", vbExclamation
    End Sub

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780543' date='18-Jun-2009 16:22']Try this:

    Code:
    Sub UnprotectPromptPassword()
      Dim strPassword As String
      Dim sht As Variant
      strPassword = InputBox("Enter the password to unlock the sheets")
      On Error GoTo ErrHandler
      For Each sht In ActiveWorkbook.Sheets
    	sht.Unprotect strPassword
      Next sht
      Exit Sub
    ErrHandler:
      MsgBox "Mwahahaha, you stupid fool!", vbExclamation
    End Sub
    [/quote]


    PERFECT! I can always rely on you Hans...and I like your Message box very much!
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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