tomsov
New Member
- Joined
- Mar 31, 2017
- Messages
- 24
Hello,
Bit stuck on this one and hope someone can help.
I've got a listbox in a userform that asks for name or part number. The user can enter the info and it searches a set of sheet tables and populates the listbox with the relevant info - this works fine.
What I need is to be able to click on the result in the listbox and be taken to the relevant record on the worksheet. So far I can make it find the record in the worksheet but it is always the first record/instance of the customer name. I'm using the code:
Sheets("Customer").Range("B" & RecordRange.Row).Address ' VARIABLES: 'RecordRange' is the customer record list that appears in the listbox called 'Results' via a DO/LOOP, 'RowCount' is the counter of the number of records in the sheet. I'm using Results.List(RowCount, 0) = FirstCell(1, 1) to populate the listbox
Alternatively I can make a hidden listbox with the correct resulting cell row numbers that will generate at the same time as the customer listbox is populated, but I cant seem to be able to select a customer name and also make the second listbox containing the row number update a the same time.
The result is a customer listbox with the correct listings from a sheet, another listbox with the correct cell reference numbers from the same sheet - but selecting the customer name from the first listbox results in 'TRUE' and not being able to grab the info from the second listbox (as the result is always the first record/row number from the second listbox)
This is where I'm at, all variables are defined etc in OPTION EXPLICIT mode:
With Sheets("Customer").Range("CustomerTAB[" & SearchColumn & "]")
Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
If Not RecordRange Is Nothing Then
FirstAddress = RecordRange.Address
RowCount = 0
Do
Set FirstCell = Sheets("Customer").Range("B" & RecordRange.Row)
SResults.AddItem
SResults.List(RowCount, 0) = FirstCell(1, 1)
SResults.List(RowCount, 1) = FirstCell(1, 2)
SResults.List(RowCount, 2) = FirstCell(1, 3)
SResults.List(RowCount, 3) = FirstCell(1, 4)
SResults.List(RowCount, 4) = FirstCell(1, 10)
SResults.List(RowCount, 5) = FirstCell(1, 11)
SResults.List(RowCount, 6) = FirstCell(1, 15)
Me.HiddenListBoxResult.AddItem RecordRange.Row
RowCount = RowCount + 1
Set RecordRange = .FindNext(RecordRange)
If RecordRange Is Nothing Then
Exit Sub
End If
Loop While RecordRange.Address <> FirstAddress
Else
SResults.AddItem
SResults.List(RowCount, 0) = "Nothing Found"
End If
End With
End Sub
Private Sub SResults_Click()
If Me.SResults.Text <> "" Then
MsgBox ("The record is: ") & Sheets("Customer").Range("B" & RecordRange.List).Value 'THIS LINE TO BE REPLACED WITH A .SELECT TO TAKE OPERATOR TO THE RECORD NOT IN MSGBOX
End If
Thanks in advance for your help!
'Unload Me
End Sub
Bit stuck on this one and hope someone can help.
I've got a listbox in a userform that asks for name or part number. The user can enter the info and it searches a set of sheet tables and populates the listbox with the relevant info - this works fine.
What I need is to be able to click on the result in the listbox and be taken to the relevant record on the worksheet. So far I can make it find the record in the worksheet but it is always the first record/instance of the customer name. I'm using the code:
Sheets("Customer").Range("B" & RecordRange.Row).Address ' VARIABLES: 'RecordRange' is the customer record list that appears in the listbox called 'Results' via a DO/LOOP, 'RowCount' is the counter of the number of records in the sheet. I'm using Results.List(RowCount, 0) = FirstCell(1, 1) to populate the listbox
Alternatively I can make a hidden listbox with the correct resulting cell row numbers that will generate at the same time as the customer listbox is populated, but I cant seem to be able to select a customer name and also make the second listbox containing the row number update a the same time.
The result is a customer listbox with the correct listings from a sheet, another listbox with the correct cell reference numbers from the same sheet - but selecting the customer name from the first listbox results in 'TRUE' and not being able to grab the info from the second listbox (as the result is always the first record/row number from the second listbox)
This is where I'm at, all variables are defined etc in OPTION EXPLICIT mode:
With Sheets("Customer").Range("CustomerTAB[" & SearchColumn & "]")
Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
If Not RecordRange Is Nothing Then
FirstAddress = RecordRange.Address
RowCount = 0
Do
Set FirstCell = Sheets("Customer").Range("B" & RecordRange.Row)
SResults.AddItem
SResults.List(RowCount, 0) = FirstCell(1, 1)
SResults.List(RowCount, 1) = FirstCell(1, 2)
SResults.List(RowCount, 2) = FirstCell(1, 3)
SResults.List(RowCount, 3) = FirstCell(1, 4)
SResults.List(RowCount, 4) = FirstCell(1, 10)
SResults.List(RowCount, 5) = FirstCell(1, 11)
SResults.List(RowCount, 6) = FirstCell(1, 15)
Me.HiddenListBoxResult.AddItem RecordRange.Row
RowCount = RowCount + 1
Set RecordRange = .FindNext(RecordRange)
If RecordRange Is Nothing Then
Exit Sub
End If
Loop While RecordRange.Address <> FirstAddress
Else
SResults.AddItem
SResults.List(RowCount, 0) = "Nothing Found"
End If
End With
End Sub
Private Sub SResults_Click()
If Me.SResults.Text <> "" Then
MsgBox ("The record is: ") & Sheets("Customer").Range("B" & RecordRange.List).Value 'THIS LINE TO BE REPLACED WITH A .SELECT TO TAKE OPERATOR TO THE RECORD NOT IN MSGBOX
End If
Thanks in advance for your help!
'Unload Me
End Sub