Microsoft Excel Search| search in all columns Excel Using ComboBox in VBA #Excel userforms

 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