Maco that prompts password entry based on pick list values

[kwp004]

Here's what I'm trying to accomplish:

• In cell A1 of Sheet1, I have data validation that requires the user to select from a pick list in Column B of Sheet 2.
• I have a button that runs a macro.
• If the user clicks the button, I want them to be prompted to enter a password based on the pick list selection, the corresponding value in Column B of Sheet 2, and an adjacent password in Column C of Sheet 2.
• If the password is correct, the macro runs. If it's incorrect, they get an error message.

So for example:

1. In Sheet 2, Range B1 = "Option 1". Range C1 = "Password 1"
2. In Sheet 2, Range B2 = "Option 2" Range C2 = "Password 2".

The user goes to A1 in Sheet 1, selects "Option 2" in the pick list. When they click a button to run a the macro, they need to enter "Password 2", or else they get an error message.

 

[Logit]

.
Run a Macro from a Drop Down list [VBA]

 

[kwp004]

If I'm reading that article correctly, I can launch a different macro based on the pick list value. So how should I apply that concept here? My goal is to run the same macro, except a different password will be required at the outset, based on the pick list value.

 

[Logit]

I believe you should alter the approach ... Based on the pick list value, a different macro will run.

This will select the different password and the macro it runs can be the same for all. It is using a SELECT CASE statement
which makes it easier when using the DropDown select.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Select Case Range("B2")
        Case "Macro1": Macro1
        Case "Macro2": Macro2
        Case Else: MsgBox "Macro not available"
    End Select
End If
End Sub

 

[mikerickson]

Have your button call this macro

Sub myMacro()
    Dim uiPassword As String
    Dim currentPassword As Variant
    
    currentPassword = Application.VLookup(Sheet1.Range("A1").Value, Sheet2.Range("B:C"), 2, False)
    
    If IsError(currentPassword) Then
        Rem Sheet1!A1 does not have anything selected.
        Exit Sub
    End If
    
    uiPassword = Application.InputBox("Enter a case insensitive password", Type:=2)
    If uiPassword = "False" Then Exit Sub: Rem cancel pressed
    
    If LCase(uiPassword) <> LCase(currentPassword) Then
        MsgBox "wrong password"
        Exit Sub
    End If
    
    ' your code
    
End Sub

 

[kwp004]

Thanks to you both. Mikerikson's solution worked like a charm.

 

[Logit]

Glad you found a solution.

 

[kwp004]

I'm hoping mikerickson can help me again (or that someone else can apply the concept above).

Basically, the solution mikerickson posted above still works, but I need help with the macro that will run after it (i.e. after ' your code in mikerickson's solution). I need to unhide very hidden worksheets based on the password that was entered. So using the example above, if "Password 1" is entered, it should open "Sheet3". If "Password 2" is entered, it should open "Sheet4". Is it possible to do that by using another vlookup to Sheet 2? So for example in Sheet 2:

Column B	Column C	Column D
Option 1	Password 1	Sheet 3
Option 2	Password 2	Sheet 4

Also, I'd like to make the sheet that unhides become Very Hidden again if the user navigates to another sheet, or closes out the file.

 

[kwp004]

Any suggestions?

 

[mikerickson]

Select Case LCase(uiPassword)
    Case "password 1"
        ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible
    Case "password 2"
        ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVisible
End Select