Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I want modify the code to allow users to format cloumns/rows in sheet array. Pl help.
    [codebox]Sub Hide()
    Dim strName As Variant
    For Each strName In Array("BG3", "SSC", "EXHAUST", "MUFFLER", "FRAME", "RIM", "HRD", "PNG", "ANS")
    With Worksheets(strName)
    .Columns("A:AR").Hidden = True
    .Protect Password:="psd"
    .Select
    .Range("AS1").Select
    End With
    Next
    Sheets("Key Ratio ").Select
    Range("A3").Select
    End Sub[/codebox]
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can expand the line

    .Protect Password:="psd"

    to specify what the user is allowed to do:

    .Protect Password:="psd", AllowFormattingColumns:=True, AllowFormattingRows:=True

    (If you omit AllowFormattingColumns and AllowFormattingColumns they are assumed to be False)

    See the help for Protect for other available options.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797853' date='14-Oct-2009 15:39']You can expand the line

    .Protect Password:="psd"

    to specify what the user is allowed to do:

    .Protect Password:="psd", AllowFormattingColumns:=True, AllowFormattingRows:=True

    (If you omit AllowFormattingColumns and AllowFormattingColumns they are assumed to be False)

    See the help for Protect for other available options.[/quote]
    Thanks Hans. It is perfect.

    For the sake of curiosity, is it possible to allow to format particular columns/rows instead of entire ws?
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What exactly do you want to accomplish?

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799165' date='21-Oct-2009 18:44']What exactly do you want to accomplish?[/quote]
    In a wb, some columns, say A to Z are used as data field & Col. AA to AE are used as summary. The wb is protected & for review purpose, I keep the data fields hidden. With the help of above code, users are allowed to format col./rows as required. I want to restrict the users to format the summary portion only, if possible.
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can make it a bit more diffcult to unhide rows / columns by not allowing users to select locked cells. But I don't think you can prevent it entirely. If you really want that, you shouldn't allow users to format rows and columns.

  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='799171' date='21-Oct-2009 19:09']You can make it a bit more diffcult to unhide rows / columns by not allowing users to select locked cells. But I don't think you can prevent it entirely. If you really want that, you shouldn't allow users to format rows and columns.[/quote]
    Thanks Hans but as I stated, it is *NOT* necessarily required to hide particular columns & the code is perfect without any further modification. I was looking for the possibility of avoiding any mis-presentation but that can be taken care of without amending the code. (After all, the users are equally responsible for any (mis) presentation of data.)

    Thanks again.
    Regards
    Prasad

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='799304' date='22-Oct-2009 10:02']Thanks Hans but as I stated, it is *NOT* necessarily required to hide particular columns & the code is perfect without any further modification. I was looking for the possibility of avoiding any mis-presentation but that can be taken care of without amending the code. (After all, the users are equally responsible for any (mis) presentation of data.)

    Thanks again.
    [/quote]

    Pl have a look :

    [codebox]Sub unhide()
    For Each sht In ActiveWorkbook.Sheets
    On Error Resume Next
    sht.Unprotect
    If Err Then
    MsgBox "Password incorrect. Please try again.", vbExclamation
    sht.Unprotect
    If Err Then
    MsgBox "Sorry! better luck next time", vbExclamation
    End If
    End If
    On Error GoTo 0
    Next sht
    Dim strName As Variant
    For Each strName In Array("BG3", "SSC", "MUFFLER", "FRAME", "RIM", "HRD", "PNG", "ANS")
    With Worksheets(strName)
    .Columns("A:AP").Hidden = False
    .Select
    .Range("A1").Select
    End With
    Next
    Sheets("Key Ratio ").Select
    Range("A3").Select
    End Sub[/codebox]


    1) if I hit the <Esc> OR press cancel button instead of supplying the password, the code unprotect the sheet & jump to next sheet.

    2) It is more convenient for me to unprotect all sheets in array in single attempt, instead of supplying password for each sheet one by one.

    3) If I supplied correct password on second attempt, the code unprotect the sheet but msg still flashes.

    Sorry if it sounds irretating but I have not tested the code this way before.
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Are all sheets protected?
    Do all protected sheets have the same password?

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799499' date='23-Oct-2009 14:40']Are all sheets protected?[/quote]
    No Hans, only sheets in array are protected using following code.
    [codebox]ub Hide()
    Dim strName As Variant
    For Each strName In Array("BG3", "SSC", "MUFFLER", "FRAME", "RIM", "HRD", "PNG", "ANS")
    With Worksheets(strName)
    .Columns("A:AR").Hidden = True
    .Columns("AU:AU").Hidden = True
    .Protect Password:="psd", AllowFormattingColumns:=True, AllowFormattingRows:=True
    .Select
    .Range("AS1").Select
    End With
    Next
    Sheets("Key Ratio ").Select
    Range("A3").Select
    End Sub[/codebox]

    Do all protected sheets have the same password?
    Yes, all protected sheets have the same password.
    Regards
    Prasad

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you need the user to supply the password to unprotect the sheets, or would it be OK to include the password in the code (like you do in the code that protects the sheets)?

  12. #12
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799520' date='23-Oct-2009 18:05']Do you need the user to supply the password to unprotect the sheets, or would it be OK to include the password in the code (like you do in the code that protects the sheets)?[/quote]
    Yes, users are required to supply the password only once to unprotect all protected sheets.
    Regards
    Prasad

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

    Code:
    Sub Unhide()
      Dim strPwd As String
      Dim strName As Variant
      ' Ask for password
      strPwd = InputBox("Please enter the password.")
      If Not strPwd = "psd" Then
    	' Give user another chance
    	strPwd = InputBox("Password incorrect. Please try again.")
    	If Not strPwd = "psd" Then
    	  ' Twice wrong - get out
    	  MsgBox "Sorry! Better luck next time.", vbExclamation
    	  Exit Sub
    	End If
      End If
      ' Loop through protected sheets
      For Each strName In Array("BG3", "SSC", "MUFFLER", "FRAME", "RIM", "HRD", "PNG", "ANS")
    	With Worksheets(strName)
    	  ' Unprotect
    	  .Unprotect Password:=strPwd
    	  ' Other actions
    	  .Columns("A:AP").Hidden = False
    	  .Select
    	  .Range("A1").Select
    	End With
      Next strName
      ' Final actions
      Sheets("Key Ratio ").Select
      Range("A3").Select
    End Sub

  14. #14
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I supplied correct password on second attempt, it fails to unprotect the sheets.
    Regards
    Prasad

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, my mistake. The line

    If Not strPassword = "psd" Then

    should be

    If Not strPwd = "psd" Then

    If you had had Option Explicit at the top of the module, the Visual Basic Editor would have pointed out the inconsistency.

Page 1 of 2 12 LastLast

Posting Permissions

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