Programming in Visual Basic.Net: Get data from Excel file in VB.NET

 

Full Code

Imports ClosedXML.Excel
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Using workb As New XLWorkbook("H:\Names2.xlsx")
                Dim worksh = workb.Worksheet("Sheet4")
                Dim lastrow As Integer = worksh.LastRowUsed.RowNumber()
                Dim idfound As Boolean = False
                For i As Integer = 2 To lastrow
                    If worksh.Cell(i, 1).GetValue(Of Integer) = CInt(TextBox1.Text) Then
                        TextBox2.Text = worksh.Cell(i, 2).GetString()
                        TextBox3.Text = worksh.Cell(i, 3).GetString()
                        TextBox4.Text = worksh.Cell(i, 4).GetString()
                        idfound = True
                        Exit For
                    End If
                Next
                If Not idfound Then
                    TextBox2.Text = ""
                    TextBox3.Text = ""
                    TextBox4.Text = ""
                End If
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

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