June 30, 2022
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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
Next
In 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
Answers Post
1 Guest(s)