Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,


    I need help in a userform or a Macro that can actually solve my problem.


    I have a Workbook that contains several worksheets For ex. "Sam", "John", "Amy", "Mellisa", "Victor" and so on. Now, what I have done is , I have hidden few cells in each of the worksheet ( not same cells in all worksheets) and I have protected them using different passwords for each worksheet.

    Suppose, password for worksheet Amy " is Amy123 and so on for different worksheets. Now I want that when a user clicks on any of the worksheet and enters the password of that worksheet, macro will unprotect the worksheet and unhide the worksheet cells. Also, when a person, closes the workbook, it should again apply again all protections and hide all the cells which were hidden earlier.....


    Thanks
    Ankit

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Users can select Tools | Protection | Unprotect sheet... to unprotect a sheet - you don't need a macro for that.

    To apply protection etc. you can use the Workbook_BeforeSave event in the ThisWorkbook module.

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Yes, I know that , but I want that when a user clicks on a worksheet and runs a macro , it will automatically ask for a password and after entering correct password, it will unhide all the cells and Unprotect the worksheet.

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

    Code:
    Sub UnprotectActiveSheet()
      Dim strPassword As String
      strPassword = InputBox("Enter the password for unprotecting the sheet")
      On Error Resume Next
      ActiveSheet.Unprotect Password:=strPassword
      If Err Then
        MsgBox "Wrong password. Sheet still protected.", vbExclamation
        Exit Sub
      End If
      ActiveSheet.Rows.Hidden = False
      ActiveSheet.Columns.Hidden = False
    End Sub

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by ankitag85 View Post
    Also, when a person, closes the workbook, it should again apply again all protections and hide all the cells which were hidden earlier.....
    I have adapted and added to Hans' code to hide and protect each time the worksheet is de-activated. The following code should be placed in the respective worksheet modules
    Code:
    Private Sub Worksheet_Activate()
      Dim strpassword As String
      If [Saving] Then Exit Sub
      strpassword = InputBox("Enter the password for unprotecting the sheet")
      On Error Resume Next
      ActiveSheet.Unprotect Password:=strpassword
      If Err Then
        MsgBox "Wrong password. Sheet still protected.", vbExclamation
        Exit Sub
      End If
      ActiveSheet.Rows.Hidden = False
      ActiveSheet.Columns.Hidden = False
    End Sub
    
    Private Sub Worksheet_Deactivate()
    
    '###### Tailor to the specific sheet ######
    Const strpassword = "Amy123"             '#
        With Sheets("Amy")                   '#
    '##########################################
    
            .Unprotect strpassword
            
    '###### Tailor to the specific sheet ######
            .Rows(6).Hidden = True           '#
            .Rows(9).Hidden = True           '#
            .Columns(4).Hidden = True        '#
    '##########################################
    
            .Protect strpassword
        End With
    End Sub
    And the following code should be placed in the ThisWorkbook module
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim oWsht As Worksheet
        
        ActiveWorkbook.Names.Add Name:="Saving", RefersToR1C1:="=TRUE"
        For Each oWsht In Worksheets
            oWsht.Activate
        Next oWsht
        ActiveWorkbook.Names.Add Name:="Saving", RefersToR1C1:="=FALSE"
    End Sub
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Here is a faster version of the Workbook_BeforeSave procedure for the ThisWorkbook module.
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim lngIndex As Long
    
        ActiveWorkbook.Names.Add Name:="Saving", RefersToR1C1:="=TRUE"
        
        lngIndex = Sheets(ActiveSheet.Name).Index
        If lngIndex = 1 Then
            Sheets(2).Activate
        Else
            Sheets(1).Activate
        End If
        Sheets(lngIndex).Activate
        
        ActiveWorkbook.Names.Add Name:="Saving", RefersToR1C1:="=FALSE"
    End Sub
    Regards
    Don

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Central Texas, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Be nicer to be save the rows and columns to be saved in a named array; that way don't need a line of code for each and every column and row in every sheet to be hidden and unhidden:

    Code:
    'Sample named range containing array
    ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="HideMe", RefersToR1C1:=Array("R6", "R9", "C4")
    Code:
    For Each a In Evaluate(Mid(ActiveWorkbook.Worksheets("Sheet1").Names("HideMe"), 2))
       If UCase(Left(a, 1)) = "R" Then
          Rows(Mid(a, 2) + 0).Hidden = True
      ElseIf UCase(Left(a, 1)) = "C" Then
          Columns(Mid(a, 2) + 0).EntireColumn.Hidden = True
      Else
          MsgBox ("Error in array: " & a)
      End If
    Next
    --Scott.

  8. #8
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks!!!

Posting Permissions

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