Userform Lists from OptionButtons and vlookup textboxes

[vbagreenhorn]

Hi!

I have a sheet called "Info" filled with A LOT of information (31000 cells!).

Lets use this as an example for my questions:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH]ID[/TH]
[TH]Class[/TH]
[TH]Name[/TH]
[TH]Points[/TH]
[TH]Year[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]Blue[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Green[/TD]
[TD]B[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[TD]C[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Red[/TD]
[TD]D[/TD]
[TD]0[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


I have a userform that has options buttons for:

• the class options (arguments sake : Blue, Green, Orange, Red. One optionbutton each.)
• the year options (arguments sake: 1, 2, 3, 4, 5. One optionbutton each.)

I'd like somewhere on the userform to list the possible Names dependent on the option buttons toggled, so for example if the Year 2 and Blue optionbuttons were toggled, then I want a list of the names of students in Year 2, Class Blue.

Once this is done, I also want to be able to display within textbox fields on the same userform, values from the spreadsheet using a vlookup, or any other function that is similar but more optimal to use. So for example I want a textbox field to display the value for Points when a particular student's name has been selected.

Just wanted to also point out, if it wasn't clear or obvious from the text above, that I want the userform to be the only part the user interacts with and/or needs. I already have code in place that auto-calls the userform to display when opened and also auto-minimizes the workbook in the back and upon closing or clicking an Exit button on the userform will close it all altogether.

Is this at all possible?!

Thank you!

 

[RickXL]

Hi,

This is one way:

Private fltrYear   As Long
Private fltrClass  As String

Private Sub OptionButton1_Click()
    fltrYear = 1
    Call filter
End Sub

Private Sub OptionButton2_Click()
    fltrYear = 2
    Call filter
End Sub

Private Sub OptionButton3_Click()
    fltrYear = 3
    Call filter
End Sub

Private Sub OptionButton4_Click()
    fltrYear = 4
    Call filter
End Sub

Private Sub OptionButton6_Click()
    fltrClass = "Green"
    Call filter
End Sub

Private Sub OptionButton7_Click()
    fltrClass = "Red"
    Call filter
End Sub

Private Sub OptionButton8_Click()
    fltrClass = "Blue"
    Call filter
End Sub

Sub filter()
    Dim arr  As Variant
    Dim i    As Long
    Dim j    As Long
    With ActiveSheet
        arr = .UsedRange
    End With
    With ListBox1
        .Clear
        For i = 1 To UBound(arr)
            If arr(i, 2) = fltrClass And arr(i, 5) = fltrYear Then
                .AddItem arr(i, 1)
                For j = 1 To 4
                    .List(.ListCount - 1, j) = arr(i, j + 1)
                Next
            End If
        Next
    End With
End Sub

I set the ColumnsCount Property of the Listbox to 5 so that all columns will be displayed.

The OptionButton macros just set the value of a Private variable for later use in the Filter macro. They then call the Filter Macro.
The Filter macro copies all the data from the worksheet into an array. It then clears the Listbox and reads through the array adding all rows that match the filter criteria to the item list.


Regards,