Search in a ListBox and select an item in the ListBox in UserForm - VBA

CakzPrimz

Board Regular
Joined
Oct 6, 2017
Messages
55
Dears,

I have a TextBox to find a part of words contains in a ListBox, and it worked. It will highlight all the records that contains the words I searching for,
If one of the highlighted record is located in the below, I could not select a single item., it will always going above, to the list that showing up as in initialize UserForm event.

How could select an item in ListBox after search in a TextBox?

The name of the ListBox is List_Description

VBA Code:
Private Sub List_Description_AfterUpdate()
    On Error Resume Next
    cb_AXREP.SetFocus
    System_Number.Visible = True
    System_Number.Caption = Application.VLookup(Me.List_Description.Text, Worksheets("prima").Range("JG2:JH1000"), 2, False) 'System Description
    ActiveSheet.Range("C3").Value = System_Number.Caption
    List_Description.MultiSelect = fmMultiSelectSingle
End Sub

Private Sub List_Description_Click()
    On Error Resume Next
    System_Number.Visible = True
    System_Number.Caption = Application.VLookup(Me.List_Description.Text, Worksheets("prima").Range("JG2:JH1000"), 2, False) 'System Description
    ActiveSheet.Range("C3").Value = System_Number.Caption
    List_Description.MultiSelect = fmMultiSelectSingle
    
End Sub

The code in UserForm Initialize event:

Code:
Private Sub UserForm_Initialize()
    With Worksheets("prima")
        Set r1 = .Range("JG2", .Range("JG5000").End(xlUp)) 'System Description
    End With
         List_Description.RowSource = "prima!" & r1.Address
         System_Number.Visible = False
         Search_Text.SetFocus
End Sub

And here's the code for Search_Text Change event:

Code:
Private Sub Search_Text_Change()
    Search_Text.Value = UCase(Search_Text.Value)
    Search_Text.BackColor = &HFFFFFF
    Search_Text.ForeColor = &H0&
    Call search
    If Search_Text.Value = vbNullString Then
        With Worksheets("prima")
        Set r1 = .Range("JG2", .Range("JG5000").End(xlUp)) 'System Description
    End With
         List_Description.RowSource = "prima!" & r1.Address
    End If
End Sub

And the code of Search:

Code:
Sub search()
    Dim i As Integer
    Dim j As Integer


    With Frm_SystemDescription.List_Description
        .MultiSelect = fmMultiSelectSingle
        .ListIndex = -1
        .MultiSelect = fmMultiSelectMulti


        For i = 0 To .ListCount - 1
            For j = 0 To .ColumnCount - 1
                If InStr(1, .Column(j, i), Frm_SystemDescription.Search_Text.Text, vbTextCompare) Then
                    .ListIndex = i
                    .Selected(i) = True
                End If
            Next j
        Next i

    End With
End Sub

If required, to get a better understanding I could attach the file, size is 617 KB. I could upload in the Google Drive, if necessary.

Thank you
Prima - Indonesia
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Watch MrExcel Video

Forum statistics

Threads
1,132,980
Messages
5,656,203
Members
418,289
Latest member
Kjohno

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
Top