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.
Here is the code I use for when clicking in the list box
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
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