Get the correct List Index from worksheet when using a search form

jamobe

New Member
Joined
Dec 23, 2014
Messages
19
Hi

I currently have the following code, the 1st one is what I use to search for items from my table "MasterProducts" It works pretty well so far.

VBA Code:
Private Sub Init_Listbox()

Set ws = ThisWorkbook.Worksheets("Master Products")
Set dTable = ws.ListObjects("MasterProducts")       '* I gave the Table a name instead of Table
lstRow = dTable.DataBodyRange.Rows.Count
With Me.ListBox1


    Me.tbxRec.Value = 0
    .Clear                                  'clear listbox
    .ColumnCount = 5                        'Set nr of columns
    .ColumnWidths = "0;120;350;90;150"       'set the column widths
    For tRow = 1 To lstRow                  'start filling the listbox
        'dstring will hold the data of the three columns in one string as lower case text used for searching
        dString = LCase(dTable.DataBodyRange(tRow, 1).Value & dTable.DataBodyRange(tRow, 2).Value & dTable.DataBodyRange(tRow, 7).Value & dTable.DataBodyRange(tRow, 9).Value)
        ' the select statement checks the length of the search text
        Select Case Len(Trim(Me.TextBox1.Value))
        Case Is > 0     '*  if its greater than 0 it checks if the text is present in the dstring if not record is skipped
            If InStr(1, dString, LCase(Me.TextBox1.Value)) = 0 Then GoTo nexttRow
        End Select
        
        .AddItem
        .List(.ListCount - 1, 0) = tRow                                 '*  record's row number
        .List(.ListCount - 1, 1) = dTable.DataBodyRange(tRow, 1).Value  '*  Sku
        .List(.ListCount - 1, 2) = dTable.DataBodyRange(tRow, 2).Value  '*  Title
        .List(.ListCount - 1, 3) = dTable.DataBodyRange(tRow, 9).Value  '*  Supplier Code
        .List(.ListCount - 1, 4) = dTable.DataBodyRange(tRow, 7).Value  '*  Supplier Name
        Me.tbxRec.Value = .ListCount    '*  updates the record counter showing the nr of records in the list
nexttRow:
    Next tRow
    
End With

End Sub

Here is the code I use for when clicking in the list box

VBA Code:
Private Sub Listbox1_click()

Set dTable = ws.ListObjects("MasterProducts")
Set ws = Worksheets("Master Products")
Dim selecteditem As String

    If Me.ListBox1.ListIndex <> -1 Then
        selecteditem = Me.ListBox1.List(Me.ListBox1.ListIndex)
        myrow = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
        myrow = Cells.Rows(Me.ListBox1.ListIndex).EntireRow.Select
       
    End If
    
End Sub

For starters when nothing is searched for, and I click an item in the listbox it seems to return the incorrect listindex value, what ever I select in the list, it selects the item 2 rows above it in the main table. Then when I do perform a search and narrow the list down, it doesn't select the item. I guess it just takes into account the position it is clicked in the listbox and not what is actually selected. Is there a way I can modify this so it actually selects the correct item in the list? I am using Office 365. Many thanks in advance!

I
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
Unless you are populating the listbox with all the items from the table the ListIndex won't correspond to the row in the table.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,570
Office Version
  1. 365
Platform
  1. Windows
Shouldn't you be looking at the 1st column in the listbox to get the row number?
VBA Code:
myrow = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
384
Office Version
  1. 2007
Platform
  1. Windows
You need to fill listbox with visible rows.
This code shows example how to do that on start with supposing you have filtered data in the beginning.
You can do similar procedure after search with textbox.
VBA Code:
Dim ws As Worksheet
Dim selecteditem As String

Private Sub UserForm_Initialize()

    Set ws = ThisWorkbook.Worksheets("Master Products")
    Set dTable = ws.ListObjects("MasterProducts")       '* I gave the Table a name instead of Table
    lstRow = dTable.DataBodyRange.Rows.Count
    With Me.ListBox1
        Me.tbxRec.Value = 0
        .Clear                                  'clear listbox
        .ColumnCount = 5                        'Set nr of columns
        .ColumnWidths = "0;120;350;90;150"       'set the column widths
        For tRow = 1 To lstRow                 'start filling the listbox
            If ws.Rows(tRow + 1).RowHeight > 0 Then
                'dstring will hold the data of the three columns in one string as lower case text used for searching
                dString = LCase(dTable.DataBodyRange(tRow, 1).Value & dTable.DataBodyRange(tRow, 2).Value & _
                             dTable.DataBodyRange(tRow, 7).Value & dTable.DataBodyRange(tRow, 9).Value)
                ' the select statement checks the length of the search text
                Select Case Len(Trim(Me.TextBox1.Value))
                Case Is > 0     '*  if its greater than 0 it checks if the text is present in the dstring if not record is skipped
                    If InStr(1, dString, LCase(Me.TextBox1.Value)) = 0 Then GoTo nexttRow
                End Select
                .AddItem
                .List(.ListCount - 1, 0) = tRow                                 '*  record's row number
                .List(.ListCount - 1, 1) = dTable.DataBodyRange(tRow, 1).Value  '*  Sku
                .List(.ListCount - 1, 2) = dTable.DataBodyRange(tRow, 2).Value  '*  Title
                .List(.ListCount - 1, 3) = dTable.DataBodyRange(tRow, 9).Value  '*  Supplier Code
                .List(.ListCount - 1, 4) = dTable.DataBodyRange(tRow, 7).Value  '*  Supplier Name
                Me.tbxRec.Value = .ListCount    '*  updates the record counter showing the nr of records in the list
            End If
nexttRow:
        Next tRow
    End With

End Sub

Private Sub Listbox1_click()

    Set ws = Worksheets("Master Products")
    Set dTable = ws.ListObjects("MasterProducts")
    If Me.ListBox1.ListIndex <> -1 Then
        selecteditem = Me.ListBox1.List(Me.ListBox1.ListIndex)
        Cells.Rows(selecteditem + 1).EntireRow.Select
    End If

End Sub
 
Solution

jamobe

New Member
Joined
Dec 23, 2014
Messages
19

ADVERTISEMENT

You need to fill listbox with visible rows.
This code shows example how to do that on start with supposing you have filtered data in the beginning.
You can do similar procedure after search with textbox.
VBA Code:
Dim ws As Worksheet
Dim selecteditem As String

Private Sub UserForm_Initialize()

    Set ws = ThisWorkbook.Worksheets("Master Products")
    Set dTable = ws.ListObjects("MasterProducts")       '* I gave the Table a name instead of Table
    lstRow = dTable.DataBodyRange.Rows.Count
    With Me.ListBox1
        Me.tbxRec.Value = 0
        .Clear                                  'clear listbox
        .ColumnCount = 5                        'Set nr of columns
        .ColumnWidths = "0;120;350;90;150"       'set the column widths
        For tRow = 1 To lstRow                 'start filling the listbox
            If ws.Rows(tRow + 1).RowHeight > 0 Then
                'dstring will hold the data of the three columns in one string as lower case text used for searching
                dString = LCase(dTable.DataBodyRange(tRow, 1).Value & dTable.DataBodyRange(tRow, 2).Value & _
                             dTable.DataBodyRange(tRow, 7).Value & dTable.DataBodyRange(tRow, 9).Value)
                ' the select statement checks the length of the search text
                Select Case Len(Trim(Me.TextBox1.Value))
                Case Is > 0     '*  if its greater than 0 it checks if the text is present in the dstring if not record is skipped
                    If InStr(1, dString, LCase(Me.TextBox1.Value)) = 0 Then GoTo nexttRow
                End Select
                .AddItem
                .List(.ListCount - 1, 0) = tRow                                 '*  record's row number
                .List(.ListCount - 1, 1) = dTable.DataBodyRange(tRow, 1).Value  '*  Sku
                .List(.ListCount - 1, 2) = dTable.DataBodyRange(tRow, 2).Value  '*  Title
                .List(.ListCount - 1, 3) = dTable.DataBodyRange(tRow, 9).Value  '*  Supplier Code
                .List(.ListCount - 1, 4) = dTable.DataBodyRange(tRow, 7).Value  '*  Supplier Name
                Me.tbxRec.Value = .ListCount    '*  updates the record counter showing the nr of records in the list
            End If
nexttRow:
        Next tRow
    End With

End Sub

Private Sub Listbox1_click()

    Set ws = Worksheets("Master Products")
    Set dTable = ws.ListObjects("MasterProducts")
    If Me.ListBox1.ListIndex <> -1 Then
        selecteditem = Me.ListBox1.List(Me.ListBox1.ListIndex)
        Cells.Rows(selecteditem + 1).EntireRow.Select
    End If

End Sub
I don't know what to say, it works perfectly!! Thanks so much. I will look at the changes you did and learn from it. I am so happy that my worksheet is near enough complete :)
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
384
Office Version
  1. 2007
Platform
  1. Windows
We are glad that you are so impressed.
 

jamobe

New Member
Joined
Dec 23, 2014
Messages
19

ADVERTISEMENT

Shouldn't you be looking at the 1st column in the listbox to get the row number?
VBA Code:
myrow = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
I'm not sure. I was watching a youtube tutorial where I got this code from, they had a 3 column list box, and used Listindex, 4 of which they kept describing it as an invisible column, I had a 4 column list box so that's why I ended up using listindex, 5. The actual code of VBA goes straight over my head, I try and learn it, but it is like reading a different language, I find my current way of just gathering different code, splicing it together and randomly changing attributes till it works the only way at the moment. One day I hope to actually know what I'm doing haha
 

jamobe

New Member
Joined
Dec 23, 2014
Messages
19
We are glad that you are so impressed

Yep I always am from this forum! I have just been testing it, I can now get it to load successfully in another userform of which I can use to edit the selected item from that listbox. When I delete an item using this userform, which deletes it from the main table, when I close this userform, I am then back to the search userform, but the list box still thinks that item is still there. Is there a way to refresh the search form listbox after closing another form? Cheers
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
384
Office Version
  1. 2007
Platform
  1. Windows
You do not need to refresh listbox,
just remove selected item from listbox after deleting in the sheet.

VBA Code:
Private Sub btnDeleteListboxItem_Click()

'removing from sheet
     ws.Rows(Me.ListBox1.ListIndex + 2).Delete
'removing from listbox
     Me.ListBox1.RemoveItem (Me.ListBox1.ListIndex)

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

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