Solved Macro to unhide locked workbook pages depending on user

May 9, 2013 at 07:14:34
Specs: Windows 10
I wonder whether it would be possible to have a VBA that runs on cell B10 on "Front Cover" worksheet - that if a user enters their email address and it matches a predefined list of accepted email addresses (e.g.) a.b@gmail.com, b.c@gmail.com c.d@gmail.com - the macro unhides protected worksheets?

See More: Macro to unhide locked workbook pages depending on user

Report •

#1
May 9, 2013 at 07:37:51
Here is a very quick attempt, seems to work but needs to be modified for your needs

Dim AdminUsers(5)

Private Sub LoadEmails()
        AdminUsers(0) = "Abc@123.com"
        AdminUsers(1) = "Hello@hotmail.com"
        AdminUsers(2) = "Just.Email@h.com"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
        
    LoadEmails
    
    If Target.Address = "$A$1" Then
        
        If ValidUser(Target.Value) = True Then
            
            MsgBox "Oh Yeah Baby"
        End If
    End If
    
    
End Sub

Public Function ValidUser(nUser As Variant) As Boolean
    
    For i = 0 To 2
        
        If nUser = AdminUsers(i) Then
            ValidUser = True
            Exit For
        Else
            ValidUser = False
        End If
        
    Next i
        
End Function


Report •

#2
May 9, 2013 at 08:18:55
✔ Best Answer
This is what I created really quick and it worked pretty well on my machine. Just create two shapes on your main sheet where they will enter their email address and assign the 'ShowHiddenSheets' macro to one and assign 'ProtectWorkbook' to the other. When the user is done, make sure they click the Protect Workbook button to run the ProtectWorkbook macro.

The reason why I did that is because anyone can just come in and un-hide the worksheets if they know how. This will prevent that. Copy and paste this to a Module window then assign the macro's like I directed above.

Sub ShowHiddenSheets()
If Range("B10").Value = "a.b@gmail.com" Or Range("B10").Value = "b.c@gmail.com" Or Range("B10").Value = "c.d@gmail.com" Then
            UnProtectWorkbook
            Exit Sub
        Else
    End If
End Sub
Sub ProtectWorkbook()
        Worksheets("Sheet1").Visible = False
        Worksheets("Sheet2").Visible = False
        Worksheets("Sheet3").Visible = False
    ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="EnterPasswordHere"
 End Sub
Sub UnProtectWorkbook()
    ActiveWorkbook.Unprotect Password:="EnterPasswordHere"
        Worksheets("Sheet1").Visible = True
        Worksheets("Sheet2").Visible = True
        Worksheets("Sheet3").Visible = True
 End Sub

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

#3
May 9, 2013 at 08:58:28
re: "The reason why I did that is because anyone can just come in and un-hide the worksheets if they know how."

I guess if I was feeling malicious, I'd just open the VBA editor, read the password from the code and unhide the worksheets. I might even change the password, or worse yet, change the code to have it do what wanted it to do.

If you are going to put a password in a macro, then you really should set the VBAProject properties to "Lock Project For Viewing" and then password protect it.

"When the user is done, make sure they click the Protect Workbook button to run the ProtectWorkbook macro"

Leaving it up to the user to Protect the workbook when they are done is very dangerous. Users forget things, users can be malicious, users are a pain.

At a minimum I'd use the BeforeClose event to hide sheets and set the protection for the workbook, but even that is not foolproof since the user might not close the workbook.

We would need to know a lot more about how your users will be using the workbook before we could offer suggestions on how to make it a bit more bulletproof.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question