VBA search in listbox when used combobox and textbox

S

saftawy1

Board Regular
Joined
Oct 12, 2021
Messages
65
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
i have this code to serch with combobox and textbox and its good in work for some column i want change it to become search appear all column in range ( a : o )

Dim x, ws As Worksheet, i As Long, j As Long, lastRow As Long
With Me.ListBox1
.Clear
.ColumnCount = 7
.ColumnWidths = "60 pt;150 pt;80 pt;150 pt;100 pt;70 pt;100 pt"
.ColumnHeads = 0
Set ws = Sheets("Ledger")
x = Application.Match(ComboBox1.Value, ws.Rows(1), 0)
If Not IsError(x) Then
lastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To lastRow
If TextBox1 <> "" And InStr(ws.Cells(i, x), TextBox1) <> 0 Then
.AddItem
.List(j, 0) = ws.Cells(i, 1)
.List(j, 1) = ws.Cells(i, 3)
.List(j, 2) = ws.Cells(i, 4)
.List(j, 3) = ws.Cells(i, 16)
.List(j, 4) = ws.Cells(i, 17)
.List(j, 5) = ws.Cells(i, 18)
.List(j, 6) = ws.Cells(i, 10)
j = j + 1
End If
Next i
End If
End With

many thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi and welcome to MrExcel.

Option A:
If you capture a data in the combo, do you want your current code to work?
If you don't capture data in the combo, then you want to search in columns A:O?

Option B:
Or do you want to replace your macro with a new one and always search columns A:O?

Which option would you like?
 
Upvote 0
DanteAmor said:
Hi and welcome to MrExcel.

Option A:
If you capture a data in the combo, do you want your current code to work?
If you don't capture data in the combo, then you want to search in columns A:O?

Option B:
Or do you want to replace your macro with a new one and always search columns A:O?

Which option would you like?
Click to expand...
option B and thanks for your interesting
 
Upvote 0
saftawy1 said:
option B and thanks for your interesting
Click to expand...

For option B, try this:

VBA Code: 
Private Sub CommandButton1_Click()
  Dim ws As Worksheet
  Dim i As Long, j As Long, k As Long
  Dim cad As String
  
  Set ws = Sheets("Ledger")
  With Me.ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "60 pt;150 pt;80 pt;150 pt;100 pt;70 pt;100 pt"
    .ColumnHeads = 0
    For i = 1 To ws.Range("B" & Rows.Count).End(3).Row
      cad = ""
      For j = 1 To Columns("O").Column
        cad = cad & ws.Cells(i, j)
      Next
      If LCase(cad) Like "*" & LCase(TextBox1.Text) & "*" Then
        .AddItem
        .List(k, 0) = ws.Cells(i, 1)
        .List(k, 1) = ws.Cells(i, 3)
        .List(k, 2) = ws.Cells(i, 4)
        .List(k, 3) = ws.Cells(i, 16)
        .List(k, 4) = ws.Cells(i, 17)
        .List(k, 5) = ws.Cells(i, 18)
        .List(k, 6) = ws.Cells(i, 10)
        k = k + 1
      End If
    Next
    If .ListCount = 0 Then
      MsgBox "No match"
      TextBox1.SetFocus
    End If
  End With
End Sub
 
Upvote 0
DanteAmor said:
For option B, try this:

VBA Code: 
Private Sub CommandButton1_Click()
  Dim ws As Worksheet
  Dim i As Long, j As Long, k As Long
  Dim cad As String
 
  Set ws = Sheets("Ledger")
  With Me.ListBox1
    .Clear
    .ColumnCount = 7
    .ColumnWidths = "60 pt;150 pt;80 pt;150 pt;100 pt;70 pt;100 pt"
    .ColumnHeads = 0
    For i = 1 To ws.Range("B" & Rows.Count).End(3).Row
      cad = ""
      For j = 1 To Columns("O").Column
        cad = cad & ws.Cells(i, j)
      Next
      If LCase(cad) Like "*" & LCase(TextBox1.Text) & "*" Then
        .AddItem
        .List(k, 0) = ws.Cells(i, 1)
        .List(k, 1) = ws.Cells(i, 3)
        .List(k, 2) = ws.Cells(i, 4)
        .List(k, 3) = ws.Cells(i, 16)
        .List(k, 4) = ws.Cells(i, 17)
        .List(k, 5) = ws.Cells(i, 18)
        .List(k, 6) = ws.Cells(i, 10)
        k = k + 1
      End If
    Next
    If .ListCount = 0 Then
      MsgBox "No match"
      TextBox1.SetFocus
    End If
  End With
End Sub
Click to expand...
its same my code ,its work good for 10 column only, i need to put 15 column in the list box thus i need to make change in the code , ty
 
Upvote 0
@saftawy1
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
This will make it easier to test & find a solution.
 
Upvote 0
It's an example with 3 column Listbox & it should work on more than 10 column listbox.
You can type multiple keywords separated by a space, e.g "f ca"
You need to adjust this part:

Private Const sList As String = "sheet1"
Private Const nLC As String = 3 'number of columns

and:

ListBox1.ColumnWidths = "250,250,150"

VBA Code: 
Option Explicit
Dim va
Private Const sList As String = "sheet1"
Private Const NOC As String = 3 'number of columns


Private Sub UserForm_Initialize()
ListBox1.ColumnCount = NOC
ListBox1.ColumnWidths = "250,250,150"
Label1.Font.Name = "Calibri"
Label1.Font.Size = 12

With Sheets(sList)
    va = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, NOC)
        ListBox1.List = va
        Label1.Caption = "Found: " & UBound(va) & " record."
        ComboBox1.List = Application.Transpose(.Range("A1").Resize(1, NOC).Value)
End With

End Sub


Private Sub TextBox1_Change()
Dim i As Long, j As Long, k As Long
Dim tx As String
Dim vb, x

tx = Trim(UCase(TextBox1.Text))
Label1.Caption = ""
If tx = "" Then ListBox1.List = va: Exit Sub

x = Application.Match(ComboBox1.Value, Sheets(sList).Rows(1), 0)
If Not IsError(x) Then

    tx = "*" & Replace((tx), " ", "*") & "*"
    ReDim vb(1 To NOC, 1 To UBound(va, 1))
        For i = 1 To UBound(va, 1)
            If UCase(va(i, x)) Like tx Then
                k = k + 1
                For j = 1 To NOC
                    vb(j, k) = va(i, j)
                Next
            End If
        Next
        
        Select Case k
            Case 0
                ListBox1.Clear
            Case 1
                ReDim Preserve vb(1 To NOC, 1 To 2)
                ListBox1.List = Application.Transpose(vb)
            Case Is > 1
                ReDim Preserve vb(1 To NOC, 1 To k)
                ListBox1.List = Application.Transpose(vb)
        End Select
                Label1.Caption = "Found: " & k & " record"
End If

End Sub

Example:

Dropbox

www.dropbox.com www.dropbox.com
 
Upvote 0
saftawy1 said:
i want change it to become search appear all column in range ( a : o )
Click to expand...
It seems to me that I misunderstood your request.
You want more than 10 columns to appear in your listbox.

You are right, my code does not do that. What my code does, without the combobox, is look for the textbox data in column A through O.

But here is this new code to search the column according to the item selected in the combobox.
You can change the column number 15 for the number of columns you want.

VBA Code: 
  'At the beginning of all the code
  Dim ws As Worksheet
  Dim a As Variant
  
Private Sub CommandButton1_Click()
  Dim i As Long, j As Long, k As Long, col As Long
  
  With ComboBox1
    If .ListIndex = -1 Then
      MsgBox "Select item from combo"
      .SetFocus
      Exit Sub
    End If
    col = .ListIndex + 1
  End With
  
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  With Me.ListBox1
    .Clear
    For i = 1 To UBound(a, 1)
      If LCase(a(i, col)) Like "*" & LCase(TextBox1.Text) & "*" Then
        k = k + 1
        For j = 1 To UBound(a, 2)
          b(k, j) = a(i, j)
        Next
      End If
    Next
    If k = 0 Then
      MsgBox "No match"
      TextBox1.SetFocus
      Exit Sub
    End If
    
    If k = 1 Then k = 2
    c = Application.Transpose(b)
    ReDim Preserve c(1 To UBound(a, 2), 1 To k)
    .List = Application.Transpose(c)
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim i As Long
  Dim wdh As String
  
  Set ws = Sheets("Ledger")
  ws.Cells.EntireColumn.AutoFit
  
  'To add more than 10 columns.
  a = ws.Range("A1", ws.Cells(Rows.Count, 15).End(xlUp)).Value
  
  With ListBox1
    .ColumnCount = UBound(a, 2)
    'Here the width of columns is set based on the width of each column
    For i = 1 To UBound(a, 2)
      wdh = wdh & Int(ws.Cells(1, i).Width + 3) & "; "
    Next
    .ColumnWidths = wdh
  End With
  
  With ComboBox1
    .List = Application.Transpose(ws.Range("A1", ws.Cells(1, UBound(a, 2))).Value)
  End With
End Sub
 
Upvote 0
Solution
DanteAmor said:
It seems to me that I misunderstood your request.
You want more than 10 columns to appear in your listbox.

You are right, my code does not do that. What my code does, without the combobox, is look for the textbox data in column A through O.

But here is this new code to search the column according to the item selected in the combobox.
You can change the column number 15 for the number of columns you want.

VBA Code: 
  'At the beginning of all the code
  Dim ws As Worksheet
  Dim a As Variant
 
Private Sub CommandButton1_Click()
  Dim i As Long, j As Long, k As Long, col As Long
 
  With ComboBox1
    If .ListIndex = -1 Then
      MsgBox "Select item from combo"
      .SetFocus
      Exit Sub
    End If
    col = .ListIndex + 1
  End With
 
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
  With Me.ListBox1
    .Clear
    For i = 1 To UBound(a, 1)
      If LCase(a(i, col)) Like "*" & LCase(TextBox1.Text) & "*" Then
        k = k + 1
        For j = 1 To UBound(a, 2)
          b(k, j) = a(i, j)
        Next
      End If
    Next
    If k = 0 Then
      MsgBox "No match"
      TextBox1.SetFocus
      Exit Sub
    End If
   
    If k = 1 Then k = 2
    c = Application.Transpose(b)
    ReDim Preserve c(1 To UBound(a, 2), 1 To k)
    .List = Application.Transpose(c)
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim i As Long
  Dim wdh As String
 
  Set ws = Sheets("Ledger")
  ws.Cells.EntireColumn.AutoFit
 
  'To add more than 10 columns.
  a = ws.Range("A1", ws.Cells(Rows.Count, 15).End(xlUp)).Value
 
  With ListBox1
    .ColumnCount = UBound(a, 2)
    'Here the width of columns is set based on the width of each column
    For i = 1 To UBound(a, 2)
      wdh = wdh & Int(ws.Cells(1, i).Width + 3) & "; "
    Next
    .ColumnWidths = wdh
  End With
 
  With ComboBox1
    .List = Application.Transpose(ws.Range("A1", ws.Cells(1, UBound(a, 2))).Value)
  End With
End Sub
Click to expand...
thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Advice to use the same code but from top of sheet down the page
Replies
2
Views
68
ipbr21054
I
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
117
RoryA
RoryA
T
  • Solved
Populate Worksheet Rows Based on Userform Checkbox selection
2
Replies
11
Views
178
TellM1955
T
Z
  • Question Question
VBA Process Flow Chart using Shapes & Connectors
Replies
2
Views
290
zlotowlosapl
Z
K
  • Question Question
Help Needed
2
Replies
13
Views
334
kwavic
K

Forum statistics

Threads
1,224,818
Messages
6,181,150
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top