Hi all,
I'm not too good with userforms
I'm using the following code for a userform - this works perfectly.
What I would like to do is add functionality to "ElseIf ComboBox1.Value = Sheet7.Range("C1") Then" where a secondary userform pops up and displays the HVals (so Sheet7.Range("C2:C" & Sheet7.Cells(Sheet7.Rows.Count, "C").End(xlUp).Row).Value) along with offset(0,1) information that goes with each in C. For example, when ComboBox1.Value = Sheet7.Range("C1") is used, another box pops up which displays C2:D2, C3:D3.... to the bottom of the range in C.
For Context C2 down contains a list of names, D2 down contains information about those names.
For more context, what the code is doing is searching for those names on Sheet1 (they're headers effectively), hiding all the names except those where there's a match - as a bonus, I would like the pop up box to *only* display the names from Sheet7 C that have been unhidden on Sheet1 (as the names aren't always on sheet1, those name headings change daily). Though this bonus is a nice to have as the list of names from C isn't actually that long *yet*.
I'm not too good with userforms
I'm using the following code for a userform - this works perfectly.
VBA Code:
Private Sub CommandButton1_Click()
Dim HostDict As Object
Dim i As Long, x As Long
Dim HVals As Variant
Set HDict = CreateObject("Scripting.Dictionary")
Sheet1.Activate
With Sheet1
If ComboBox1.Value = "All" Then
.Columns.EntireColumn.Hidden = False
ElseIf ComboBox1.Value = Sheet7.Range("A1") Then
.Range("D1", Range("D1").End(xlToRight)).EntireColumn.Hidden = True
HVals = Sheet7.Range("A2:A" & Sheet7.Cells(Sheet7.Rows.Count, "A").End(xlUp).Row).Value
For i = 1 To UBound(HVals)
HDict(HVals(i, 1)) = HVals(i, 1)
Next i
For Each Cell In .Range("D1", .Range("D1").End(xlToRight))
If HDict.Exists(Cell.Value) Then Cell.EntireColumn.Hidden = False
Next Cell
ElseIf ComboBox1.Value = Sheet7.Range("B1") Then
.Range("D1", .Range("D1").End(xlToRight)).EntireColumn.Hidden = True
HVals = Sheet7.Range("B2:B" & Sheet7.Cells(Sheet7.Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(HVals)
HDict(HVals(i, 1)) = HVals(i, 1)
Next i
For Each Cell In .Range("D1", .Range("D1").End(xlToRight))
If HDict.Exists(Cell.Value) Then Cell.EntireColumn.Hidden = False
Next Cell
ElseIf ComboBox1.Value = Sheet7.Range("C1") Then
.Range("D1", .Range("D1").End(xlToRight)).EntireColumn.Hidden = True
HVals = Sheet7.Range("C2:C" & Sheet7.Cells(Sheet7.Rows.Count, "C").End(xlUp).Row).Value
For i = 1 To UBound(HVals)
HDict(HVals(i, 1)) = HVals(i, 1)
Next i
For Each Cell In .Range("D1", .Range("D1").End(xlToRight))
If HDict.Exists(Cell.Value) Then Cell.EntireColumn.Hidden = False
Next Cell
End If
End With
End SubWhat I would like to do is add functionality to "ElseIf ComboBox1.Value = Sheet7.Range("C1") Then" where a secondary userform pops up and displays the HVals (so Sheet7.Range("C2:C" & Sheet7.Cells(Sheet7.Rows.Count, "C").End(xlUp).Row).Value) along with offset(0,1) information that goes with each in C. For example, when ComboBox1.Value = Sheet7.Range("C1") is used, another box pops up which displays C2:D2, C3:D3.... to the bottom of the range in C.
For Context C2 down contains a list of names, D2 down contains information about those names.
For more context, what the code is doing is searching for those names on Sheet1 (they're headers effectively), hiding all the names except those where there's a match - as a bonus, I would like the pop up box to *only* display the names from Sheet7 C that have been unhidden on Sheet1 (as the names aren't always on sheet1, those name headings change daily). Though this bonus is a nice to have as the list of names from C isn't actually that long *yet*.
