Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there any method to protect/unprotect multiple sheet within same workbook in single shot? Password is common for all sheets.
    Regards
    Prasad

  2. #2
    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
    How about these? [I presume you did not mean that "common" was the literal password, but I had to use one as an example so I used that... ]

    Steve
    Code:
    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

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='776256' date='21-May-2009 11:47']How about these? [I presume you did not mean that "common" was the literal password, but I had to use one as an example so I used that... ]

    Steve
    Code:
    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
    [/quote]

    Thanks Steve. Doing fine but I must correct my earlier statement. Actually, I dont want all the sheets to protect. Some sheets will remain hide while sending information to outside customers. To serve the purpose, recorded macro are working. Any suggestiion/help, plzzzzz
    Regards
    Prasad

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='prasad' post='776386' date='22-May-2009 15:17']Thanks Steve. Doing fine but I must correct my earlier statement. Actually, I dont want all the sheets to protect. Some sheets will remain hide while sending information to outside customers. To serve the purpose, recorded macro are working. Any suggestiion/help, plzzzzz[/quote]
    Hi prasad,

    You can tell Excel which worksheets to protect with code like:
    Code:
    Sub ProtectSheetsSamePassword()
    Dim sht As Variant, i As Integer, ShtList As String
    ShtList = "Sheet1,Sheet2,Sheet3"
    For Each sht In ActiveWorkbook.Sheets
      For i = 0 To UBound(Split(ShtList, ","))
    	If sht.Name = Split(ShtList, ",")(i) Then sht.Protect "common"
      Next
    Next
    End Sub
    where the list of sheets to be protected is found in the 'ShtList' variable. You should still be able to use the unprotect sub Steve provided.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='776386' date='22-May-2009 05:17']Thanks Steve. Doing fine but I must correct my earlier statement. Actually, I dont want all the sheets to protect. Some sheets will remain hide while sending information to outside customers. To serve the purpose, recorded macro are working. Any suggestiion/help, plzzzzz[/quote]
    Thanks for the g8 help provided,but still some requirement. Is there any way to make excel understand to ask password before unhide any hidden sheet?

    regards
    prasad
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use code like this:

    Dim strPassword As String
    strPassword = InputBox("Please enter the password")
    ...
    ...
    On Error GoTo ErrHandler
    sht.Unprotect strPassword
    ...
    ...
    Exit Sub

    ErrHandler:
    MsgBox "Wrong password!"

    If the user entered an incorrect password, the code will display the custom message.

  7. #7
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779519' date='12-Jun-2009 13:00']You could use code like this:

    Dim strPassword As String
    strPassword = InputBox("Please enter the password")
    ...
    ...
    On Error GoTo ErrHandler
    sht.Unprotect strPassword
    ...
    ...
    Exit Sub

    ErrHandler:
    MsgBox "Wrong password!"

    If the user entered an incorrect password, the code will display the custom message.[/quote]

    Thanks Hans for sparing time. Acutally Excel has facility in Tool Menu to Hide/Unhide sheet, which can not be controlled through password. I want to lock this thorugh some code and I am not very familier to VBA. I have modified the given code something like this. It working fine when i hide the sheets but difficulty in unhie the sheets :
    Sub Sheethide()
    Sheets("Tr. Ratio").Visible = False
    Sheets("format month").Visible = False
    Sheets("PV").Visible = False
    Sheets("PV YTD").Visible = False
    Sheets("Sale & PBIT").Visible = False
    End Sub
    Sub Sheetunhide()
    Dim strPassword As String
    strPassword = InputBox("Please enter the password")
    123
    On Error GoTo ErrHandler
    For Each sht In ActiveWorkbook.Sheets
    sht.Unprotect strPassword
    Exit Sub

    ErrHandler:
    MsgBox "Wrong password!"
    Next
    End Sub

    Plz help me out.

    Regards
    Prasad
    Regards
    Prasad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your SheetHide macro hides some sheets. The SheetUnhide macro does not unhide them, but unprotects all sheets.
    Don't you want your macros to have matching actions, i.e. they should either hide and unhide sheets, or protect and unprotect sheets?

  9. #9
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779534' date='12-Jun-2009 14:35']Your SheetHide macro hides some sheets. The SheetUnhide macro does not unhide them, but unprotects all sheets.
    Don't you want your macros to have matching actions, i.e. they should either hide and unhide sheets, or protect and unprotect sheets?[/quote]
    Thanks Hans, got your point & changed the code accordingly:
    Sub Sheethide()
    Sheets("Tr. Ratio").Visible = False
    Sheets("format month").Visible = False
    Sheets("PV").Visible = False
    Sheets("PV YTD").Visible = False
    Sheets("Sale & PBIT").Visible = False
    End Sub
    Sub Sheetunhide()
    Dim strPassword As String
    strPassword = InputBox("Please enter the password")
    123
    On Error GoTo ErrHandler
    Sheets("Tr. Ratio").Visible = True
    Sheets("format month").Visible = True
    Sheets("PV").Visible = True
    Sheets("PV YTD").Visible = True
    Sheets("Sale & PBIT").Visible = True

    Exit Sub

    ErrHandler:
    MsgBox "Wrong password!"

    End Sub


    But still have some problems
    1. No msg box is appearing. I mean user can unhide the sheets putting anything in Input box.
    2. Toolbar (Format ->sheet ->hide/unhide) is not controlled through this code.

    Plz do something.

    Regards
    Prasad
    Regards
    Prasad

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You have to protect the workbook for structure with a password.

    You can then unprotect the workbook in code, hide/unhide sheets, and protect it again:

    Dim strPassword As String
    strPassword = InputBox("Enter password")
    ActiveWorkbook.Unprotect strPassword
    ...
    ...
    ActiveWorkbook.Protect strPassword, True

  11. #11
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779618' date='13-Jun-2009 07:33']You have to protect the workbook for structure with a password.

    You can then unprotect the workbook in code, hide/unhide sheets, and protect it again:

    Dim strPassword As String
    strPassword = InputBox("Enter password")
    ActiveWorkbook.Unptotect strPassword
    ...
    ...
    ActiveWorkbook.Protect strPassword, True[/quote]
    Thanks Hans, greaaaaaaaaat. you are a genious.

    Regards
    Regards
    Prasad

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='779618' date='13-Jun-2009 03:33']strPassword = InputBox("Enter password")[/quote]

    Hans

    Are you aware of any technique which will protect from prying eyes, a password entered in this manner?
    Regards
    Don

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use a userform instead of the InputBox function.
    Place a text box on the userform and set its PasswordChar property to the character that you want to use to mask the text, for example an asterisk *.
    Also place a command button with caption OK on the userform, and use the Click event of this command button to pass the value of the text box to a global variable, for example, so that the code that called the userform can use its value.

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='779652' date='13-Jun-2009 09:04']You could use a userform instead of the InputBox function.
    Place a text box on the userform and set its PasswordChar property to the character that you want to use to mask the text, for example an asterisk *.
    Also place a command button with caption OK on the userform, and use the Click event of this command button to pass the value of the text box to a global variable, for example, so that the code that called the userform can use its value.[/quote]

    Thank you Hans.
    Regards
    Don

Posting Permissions

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