Hello. After searching, I found this code to search and display data inside the listbox. I want to modify it or get a different code. It enables me to display the data of 3 Excel sheets from inside the workbook on the list. I tried this, but to no avail.
VBA Code:
Private Sub TextBox1_Change()
Dim ws As Worksheet
Dim rng As Range
Dim r As Long, c As Long, Lastrow As Long, r1 As Long
Dim Search As String
Dim FilterArr() As Variant
Search = Me.TextBox1.Value
Set ws = ThisWorkbook.Worksheets("Sheet2")
Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'size worksheet data range
Set rng = ws.Cells(2, 1).Resize(Lastrow, 28)
arr = rng.Value2
ReDim FilterArr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
With Me.ListBox1
'disconnect rowsource
.RowSource = ""
.ColumnHeads = False
'size listbox
.ColumnCount = rng.Columns.Count
.Clear
If Len(Search) > 0 Then
For r = 1 To UBound(arr, 1)
If UCase(arr(r, 5)) Like UCase(Search) & "*" Then
r1 = r1 + 1
For c = 1 To UBound(arr, 2)
FilterArr(r1, c) = arr(r, c)
Next c
End If
Next r
.List = ResizeArray(FilterArr, r1)
Else
'display full list
.List = arr
'or use rowsource
're-connect rowsource to display all data with column heads
.RowSource = ws.Name & "!" & rng.Address
.ColumnHeads = True
End If
End With
End Sub
Function ResizeArray(ByVal arr As Variant, ByVal RowsCount As Long) As Variant
Dim r As Long, c As Long
Dim Arr2() As Variant
If RowsCount > 0 Then
'size array to match filtered data
ReDim Arr2(1 To RowsCount, 1 To UBound(arr, 2))
For r = 1 To RowsCount
For c = 1 To UBound(arr, 2)
'pass matching elements of arr to arr2
Arr2(r, c) = arr(r, c)
Next c
Next
End If
ResizeArray = IIf(RowsCount > 0, Arr2, Array("No Match Found"))
End Function
Code:
Dim Rng As Range
Dim r As Long, c As Long, Lastrow As Long, r1 As Long
Dim FilterArr() As Variant
Dim wb As Workbook
Set wb = ThisWorkbook
Me.ListBox1.Clear
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet2", "Sheet3"
Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set Rng = ws.Cells(2, 1).Resize(Lastrow, 11)
arr = Rng.Value2
ReDim FilterArr(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)
With Me.ListBox1
.RowSource = ""
.ColumnHeads = False
.ColumnCount = Rng.Columns.Count
For r = 1 To UBound(arr, 1)
r1 = r1 + 1
For c = 1 To UBound(arr, 2)
FilterArr(r1, c) = arr(r, c)
Next c
Next r
.List = ResizeArray(FilterArr, r1)
.List = arr
.RowSource = ws.Name & "!" & Rng.Address
.ColumnHeads = True
End With
End Select
Next ws
End Sub
