Dim columnindex_v As Integer
Private Sub populate_combo1()
Dim i, lastcolumn As Integer
lastcolumn = Sheet5.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To lastcolumn
Me.ComboBox1.AddItem Sheet5.Cells(1, i).Value
Next i
End Sub
Private Sub ComboBox1_Change()
getcolumnindex
getcolumnname
populate_combo2
End Sub
Private Sub getcolumnindex()
columnindex_v = WorksheetFunction.Match(Me.ComboBox1.Text, Sheet5.Range("1:1"), 0)
Me.columnindex_txt.Caption = columnindex_v
End Sub
Private Sub getcolumnname()
Me.columnname_txt.Caption = Split(Sheet5.Columns(columnindex_v).EntireColumn.Address(0, 0), ":")(0)
End Sub
Private Sub search_listbox1()
With Me.ListBox1
.Clear
.AddItem "RN"
.List(.ListCount - 1, 1) = "ID"
.List(.ListCount - 1, 2) = "Firstname"
.List(.ListCount - 1, 3) = "Lastname"
.List(.ListCount - 1, 4) = "Sport"
.List(.ListCount - 1, 5) = "Points"
.ColumnCount = 6
Dim lastrow, i As Integer
lastrow = Sheet5.Cells(Rows.Count, Me.columnname_txt.Caption).End(xlUp).Row
For i = 2 To lastrow
If Me.ComboBox2.Text = Sheet5.Cells(i, columnindex_v) Then
.AddItem
.List(.ListCount - 1, 0) = .ListCount - 1
.List(.ListCount - 1, 1) = Sheet5.Cells.Range("A" & i)
.List(.ListCount - 1, 2) = Sheet5.Cells.Range("B" & i)
.List(.ListCount - 1, 3) = Sheet5.Cells.Range("C" & i)
.List(.ListCount - 1, 4) = Sheet5.Cells.Range("D" & i)
.List(.ListCount - 1, 5) = Sheet5.Cells.Range("E" & i)
End If
Next i
End With
End Sub
Private Sub ComboBox2_Change()
search_listbox1
End Sub
Private Sub UserForm_Initialize()
populate_combo1
End Sub
Private Sub populate_combo2()
Me.ComboBox2.Clear
Dim lastrow, i As Integer
lastrow = Sheet5.Cells(Rows.Count, Me.columnname_txt.Caption).End(xlUp).Row
For i = 2 To lastrow
Dim value_str As String
value_str = Sheet5.Cells(i, Me.columnname_txt.Caption)
If WorksheetFunction.CountIf(Sheet5.Range(Me.columnname_txt.Caption & 2, Me.columnname_txt.Caption & i), value_str) = 1 Then
Me.ComboBox2.AddItem value_str
End If
Next i
End Sub
