Combining Conditionally Visible Worksheets with Case Statement in VBA

LostInTheTrees

New Member
Joined
Jul 23, 2014
Messages
5
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.:confused:

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.

Code:
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

New Member
Joined
Jul 23, 2014
Messages
5
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?

Code:
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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:

Code:
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

New Member
Joined
Jul 23, 2014
Messages
5
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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:

Code:
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

New Member
Joined
Jul 23, 2014
Messages
5
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.

Code:
Else
    lAttempts = lAttempts + 1
    If lAttempts >= 3 Then
        ThisWorkbook.Close False
    End If
End If
Thank you so, so much for your help!
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top