I am building an automated inventory for our warehouse. I have most of my bugs worked out, but I am having difficulty with getting the correct row selected in the Userform. When I click on the "Search" button, it will open the Searchform. This way I can perform a filtered search. When I see the data I want selected, I double click it and it will fill in the text box on the UserForm. It is also suppose to select the same data in the UserForm listbox. To achieve this, I used the code;
Private Sub lstDisplay_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
UserForm.txtGFE = Me.lstDisplay.List(lstDisplay.ListIndex)
UserForm.txtDescription = Me.lstDisplay.List(lstDisplay.ListIndex, 1)
UserForm.txtPartNum = Me.lstDisplay.List(lstDisplay.ListIndex, 2)
UserForm.txtSerialNum = Me.lstDisplay.List(lstDisplay.ListIndex, 3)
UserForm.txtLocation = Me.lstDisplay.List(lstDisplay.ListIndex, 4)
UserForm.txtRemarks = Me.lstDisplay.List(lstDisplay.ListIndex, 5)
UserForm.cmbPlatform = Me.lstDisplay.List(lstDisplay.ListIndex, 6)
UserForm.lstDisplay.ListIndex = SearchForm.Results.ListIndex
End Sub
It works with some of the data, but not all of it. Any help or suggestions will be greatly appreciated.
V/R
Michael
Hi Michael,
This loop is not right:
For i = 0 To Range("H65356").End(xlUp).Row - 1
If Results.Selected(i) Then
UserForm.lstDisplay.Value = SearchForm.Results.Value
End If
Next i
It should loop through List items, not through sheet, because the results list will have just a subset of the sheet data, sheet rows will not be the same as listindex:
For i = 0 To Results.ListCount - 1
NextIn the search form, I suggest adding the row number of the match found in a new column, this way you will know where that listitem was found in sheet and you can use it later:
Results.List(RowCount, 8) = FirstCell.Row
Thank you so much!! That did exactly what I need it to do.
V/R
Michael
