Combining Conditionally Visible Worksheets with Case Statement in VBA

[LostInTheTrees]

Hi!

I'm using Excel 2010 (Windows 7) to develop a workbook with the goal of tying conditionally visible worksheets to a case statement. Conceptually, I want a user to enter a password and click on the enter password button, and have the sheets they are authorized to see appear in the tabs below. This is not meant as a super secure file, and I recognize that Excel VBA is not the most secure option.

Currently, when the "submit password" button is clicked, the entered value ("password") is moved onto the workcenter sheet. That cell on the workcenter is named "pswd." The cell below utilizes the value of pswd to perform a VLOOKUP to determine the Case tied to the password. My macros have been compiling, but not actually altering the visibility of the sheets as anticipated.

P.S. My original idea included an IFERROR(VLOOKUP()) combination, which would yield a failed Case Q if the VLOOKUP failed. This is NOT necessary, just an idea I have been playing around with.

Private Sub gatekeeper()    Dim attempted As String
    attempted = Range("enterpassword").Value
    Range("enterpassword").Value = ""
    Range("pswd").Value = attempted
    attempted = ""
    casebuilder
End Sub


Private Sub casebuilder()
Dim errq As String
Dim casevalue As String
Dim dwsp As String


dwsp = Range("pswd").Value
cased = Application.WorksheetFunction.VLookup(dwsp, Sheet88.Range("L11:N25"), 3, False)
casevalue = cased


Select Case casevalue
Case A
    Sheet11.Visible = xlSheetVisible
    Sheet13.Visible = xlSheetVisible
    Sheet41.Visible = xlSheetVisible
    Sheet43.Visible = xlSheetVisible
Case B
    Sheet11.Visible = xlSheetVisible
    Sheet12.Visible = xlSheetVisible
    Sheet13.Visible = xlSheetVisible
    Sheet41.Visible = xlSheetVisible
    Sheet42.Visible = xlSheetVisible
    Sheet43.Visible = xlSheetVisible
Case C
    Sheet21.Visible = xlSheetVisible
    Sheet23.Visible = xlSheetVisible
    Sheet24.Visible = xlSheetVisible
    Sheet26.Visible = xlSheetVisible
Case Q
    errq = errq + 1
    Range("reporterror").Value = errq
    If errq = 3 Then Unload Workbook

End Select
End Sub

If this is unclear, I will gladly try to clarify. If this is impossible, that would be incredibly disappointing. Also, if it's going to be a lot of work, please do not feel like you have to do it for me. I just need some help being pointed in the right direction.

No matter what, thanks for your time/help.

Sincerely,

LostInTheTrees

 

[ChrisM]

Your case statements must enclose text values in quotes. The way you've written it you are testing it for Case = a variable called A, which is going to have a blank value.

Case "A"

Case "B"

etc

 

[LostInTheTrees]

ChrisM,

First, thank you, that fixed the case statement perfectly. I had spent hours pouring over my layout, the named ranges involved, and everything but recognizing how I identified the cases. Oops.

Now, the correct password properly unlocks the sheets I want, but an incorrect response generates an unsurprising error:

Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class.

Do you know of a good way to handle this error? I don't really need an error counter, but I just don't want a VBA error to pop up.

I have tried nesting the VLookup function within the IfError function in VBA. I designated my error case (Q) as the result upon error, but it breaks on the VLookup still. Any ideas?

dwsp = Range("pswd").Value
cased = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(dwsp, Sheet88.Range("L11:N25"), 3, False), "Q")
casevalue = cased

Thank you so much for your help!

 

[ChrisM]

There is a difference between Application.Vlookup(xxx) and Application.WorksheetFunction.Vlookup(xxx)

As you've noticed, the difference is in how errors are handled. First, recognize there are two different types of errors. One is the error returned by the Vlookup function if no match is found, just like #n/a or #value in your worksheet. The other error is a true VBA runtime error that stops your program.

Since you are using the WorksheetFunction version, you are getting the second type of error, which means you need an OnError statement and and error handling routine. It would be much easier if you just changed over to the alternate format like this. Declare your result variable as a variant, and then write the vlookup value to it:

Private Sub casebuilder()
Dim errq As String
Dim casevalue As Variant
Dim dwsp As String


dwsp = Range("pswd").Value
casevalue = Application.VLookup(dwsp, Sheet88.Range("L11:N25"), 3, False)

Select Case casevalue
Case "A"
    Sheet11.Visible = xlSheetVisible
    Sheet13.Visible = xlSheetVisible
    Sheet41.Visible = xlSheetVisible
    Sheet43.Visible = xlSheetVisible
Case "B"
    Sheet11.Visible = xlSheetVisible
    Sheet12.Visible = xlSheetVisible
    Sheet13.Visible = xlSheetVisible
    Sheet41.Visible = xlSheetVisible
    Sheet42.Visible = xlSheetVisible
    Sheet43.Visible = xlSheetVisible
Case "C"
    Sheet21.Visible = xlSheetVisible
    Sheet23.Visible = xlSheetVisible
    Sheet24.Visible = xlSheetVisible
    Sheet26.Visible = xlSheetVisible
Case Else
    errq = errq + 1
    Range("reporterror").Value = errq
    If errq = 3 Then Unload Workbook

End Select
End Sub

I left your errq parts untouched, but I don't understand what you are doing there. First, you declared it as a String but you seem to be using it as a number. Second, you are testing if it is ever equal to 3, but you are not inside of a loop, so your code will only run one time, so the highest value errq will ever get to is one.

 

[LostInTheTrees]

Hi ChrisM,

First, thanks for your continued help; I really appreciate it.

I inserted your changes into my project, and it continues to work well for the right answers but pull up an error for wrong answers. When I intentionally entered the wrong password, I got an error:

Run-time Error 13: Type Mismatch

The butchered errq portion was my attempt to insert some sort of "3 strikes and you are out (of the workbook)." Admittedly, I had no idea how to put a loop in the case or the case in a loop, but I left the code in there because I figured someone might have an idea to fix it. It's not a must-fix, but it would be kind of a nice cherry on top.

Thanks again!

 

[ChrisM]

Oh, I thought the Case Else would have captured the error values, guess not. In that case, just test for errors first. And here's one way to do your 3 strikes method:

Private Sub casebuilder()
Static lAttempts as Long
Dim casevalue As Variant
Dim dwsp As String


dwsp = Range("pswd").Value
casevalue = Application.VLookup(dwsp, Sheet88.Range("L11:N25"), 3, False)

If Not IsError(casevalue) Then
 Select Case casevalue
 Case "A"
    Sheet11.Visible = xlSheetVisible
    Sheet13.Visible = xlSheetVisible
    Sheet41.Visible = xlSheetVisible
    Sheet43.Visible = xlSheetVisible
 Case "B"
    Sheet11.Visible = xlSheetVisible
    Sheet12.Visible = xlSheetVisible
    Sheet13.Visible = xlSheetVisible
    Sheet41.Visible = xlSheetVisible
    Sheet42.Visible = xlSheetVisible
    Sheet43.Visible = xlSheetVisible
 Case "C"
    Sheet21.Visible = xlSheetVisible
    Sheet23.Visible = xlSheetVisible
    Sheet24.Visible = xlSheetVisible
    Sheet26.Visible = xlSheetVisible
 End Select
Else
 lAttempts = lAttempts + 1
 If lAttempts >= 3 Then
     ThisWorkbook.Close
 End If
End If

End Sub

 

[LostInTheTrees]

Hi ChrisM,

Thank you so much! That works great! The only thing I didn't want was the workbook saving upon closing (due to password errors), so I found that all I needed to do was add the False indicator after the workbook.close event.

Else
    lAttempts = lAttempts + 1
    If lAttempts >= 3 Then
        ThisWorkbook.Close False
    End If
End If

Thank you so, so much for your help!