VBA search in listbox when used combobox and textbox-To be modified

V

velu888

New Member
Joined
Aug 5, 2022
Messages
7
I have worked to the reference code available at 'Previous Post Thanks a lot for the Code.
It works fine for search as you type. in Textbox1.
I request Codegurus and Codegenies to modify this so that by using two textboxes."From Date" and "To date". based on these two dates, the result of data in between the dates filter should be displayed on the listbox.
Textbox 2 and Textbox3 are dates, I want the dates in between to be displayed in the listbox1

VBA Code: 
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 15)
                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
[CODE=vba][CODE=vba]
[/CODE]
[/CODE]
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.
 
Upvote 0
the previous file was a basic one with 3 columns, the below is modified with more than 10 columns display in list box. Please use the following link file to be modified as per my request.
Modified file
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Code in excel to be modified for display in userform of data between dates - OzGrid Free Excel/VBA Help Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Call addListBox(strSQL & " AND DOB='" & Format(TextBox1.Text, "dd-mm-yyyy") & "'")
You may need to edit the date format here.

VBA Code: 
Option Explicit
Dim adoCon As Object, rs As Object, strSQL$
Private Const NOC As String = 15    'number of columns
Sub connect()
    If adoCon.State = 0 Then
        adoCon.Open "Provider=Microsoft.Ace.Oledb.12.0;Extended Properties='Excel 12.0;HDR=Yes';" & _
                    "Data Source=" & ThisWorkbook.FullName
    End If
End Sub
Private Sub UserForm_Terminate()
    If Not adoCon Is Nothing Then
        adoCon.Close
        Set rs = Nothing
        Set adoCon = Nothing
    End If
End Sub
Private Sub UserForm_Initialize()
    Dim adr
    Set adoCon = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Connection")
    ListBox1.ColumnCount = NOC
    ListBox1.ColumnWidths = "250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150"
    Label1.Font.Name = "Calibri"
    Label1.Font.Size = 12

    With Sheets("Sheet1")
        ComboBox1.List = Application.Transpose(.Range("A1").Resize(1, NOC).Value)
        adr = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, NOC).Address(0, 0)
        strSQL = "SELECT * FROM [" & .Name & "$" & adr & "] WHERE NOT COMPANY IS NULL "
        Call addListBox(strSQL$)
    End With
End Sub
Sub addListBox(strSQL_$)
    If adoCon.State = 0 Then Call connect
    Set rs = adoCon.Execute(strSQL_)
    If Not (rs.EOF Or rs.bof) Then
        ListBox1.Column = rs.getrows
        Label1.Caption = "Found: " & ListBox1.ListCount & " record."
    End If
    rs.Close
End Sub
Private Sub TextBox1_Change()
    If TextBox1.Text <> "" Then
        If ComboBox1.Text <> "" And TextBox1.Text <> "" Then
            If ComboBox1.Text <> "DOB" Then
                Call addListBox(strSQL & " AND " & ComboBox1.Text & " LIKE '%" & Replace(TextBox1.Text, " ", "%") & "%'")
            Else
                If Len(TextBox1.Text) = 10 Then
                    If IsDate(CDate(TextBox1.Text)) Then
                        Call addListBox(strSQL & " AND DOB='" & Format(TextBox1.Text, "dd-mm-yyyy") & "'")
                    End If
                Else
                    Call addListBox(strSQL)
                End If
            End If
        Else
            Call addListBox(strSQL)
        End If
    End If
End Sub
 
Upvote 0
Fluff said:
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Code in excel to be modified for display in userform of data between dates - OzGrid Free Excel/VBA Help Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Sir,
The method evolved was different, I've modified it to my needs. The question is to still reform it to the needs of me and the community. I hope there is no cross posting in it, Kindly help me to refract it to me and to open web.
Bless the forum and the fraternity.
 
Upvote 0
veyselemre said:
Call addListBox(strSQL & " AND DOB='" & Format(TextBox1.Text, "dd-mm-yyyy") & "'")
You may need to edit the date format here.

VBA Code: 
Option Explicit
Dim adoCon As Object, rs As Object, strSQL$
Private Const NOC As String = 15    'number of columns
Sub connect()
    If adoCon.State = 0 Then
        adoCon.Open "Provider=Microsoft.Ace.Oledb.12.0;Extended Properties='Excel 12.0;HDR=Yes';" & _
                    "Data Source=" & ThisWorkbook.FullName
    End If
End Sub
Private Sub UserForm_Terminate()
    If Not adoCon Is Nothing Then
        adoCon.Close
        Set rs = Nothing
        Set adoCon = Nothing
    End If
End Sub
Private Sub UserForm_Initialize()
    Dim adr
    Set adoCon = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Connection")
    ListBox1.ColumnCount = NOC
    ListBox1.ColumnWidths = "250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150,250,250,150"
    Label1.Font.Name = "Calibri"
    Label1.Font.Size = 12

    With Sheets("Sheet1")
        ComboBox1.List = Application.Transpose(.Range("A1").Resize(1, NOC).Value)
        adr = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, NOC).Address(0, 0)
        strSQL = "SELECT * FROM [" & .Name & "$" & adr & "] WHERE NOT COMPANY IS NULL "
        Call addListBox(strSQL$)
    End With
End Sub
Sub addListBox(strSQL_$)
    If adoCon.State = 0 Then Call connect
    Set rs = adoCon.Execute(strSQL_)
    If Not (rs.EOF Or rs.bof) Then
        ListBox1.Column = rs.getrows
        Label1.Caption = "Found: " & ListBox1.ListCount & " record."
    End If
    rs.Close
End Sub
Private Sub TextBox1_Change()
    If TextBox1.Text <> "" Then
        If ComboBox1.Text <> "" And TextBox1.Text <> "" Then
            If ComboBox1.Text <> "DOB" Then
                Call addListBox(strSQL & " AND " & ComboBox1.Text & " LIKE '%" & Replace(TextBox1.Text, " ", "%") & "%'")
            Else
                If Len(TextBox1.Text) = 10 Then
                    If IsDate(CDate(TextBox1.Text)) Then
                        Call addListBox(strSQL & " AND DOB='" & Format(TextBox1.Text, "dd-mm-yyyy") & "'")
                    End If
                Else
                    Call addListBox(strSQL)
                End If
            End If
        Else
            Call addListBox(strSQL)
        End If
    End If
End Sub
Click to expand...
Sir
I'm a moderate VBA Excelller, I couldn't understand your code, will you please post the sample workbook for the reference.
 
Upvote 0
velu888 said:
I request Codegurus and Codegenies to modify this so that by using two textboxes."From Date" and "To date". based on these two dates, the result of data in between the dates filter should be displayed on the listbox.
Textbox 2 and Textbox3 are dates, I want the dates in between to be displayed in the listbox1
Click to expand...
1. Do you still need to search in any column by Textbox1 or you only need to search by 2 dates?
2. What is your date regional setting, dmy or mdy?
 
Upvote 0
Another question:
3. What Excel version do you use?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
279
campbell1093
C
J
  • Solved
Filter a listbox containing 12 columns using a single searchbox
Replies
8
Views
816
Anthony47
A
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
349
Fuji
F
J
  • Question Question
search all worksheets based upon textbox value and add to listbox
Replies
7
Views
425
Jaye7
J
C
  • Question Question
Using pattern searches with a variable to find the correct values
2
Replies
10
Views
265
chazrab
C

Forum statistics

Threads
1,224,814
Messages
6,181,120
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