Listbox to cell selection

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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
you could use an eighth column in the listbox to hold the additem row number
VBA Code:
SResults.List(RowCount, 7) = FirstCell(1, 15).row
then use that for the row to goto upon selection in the listbox
VBA Code:
Private Sub SResults_Click()
    Dim rw As Long
    
With Me.SResults
    'loop through listbox rows until at the row selected
    For rw = 0 To .ListCount - 1
        If .Selected(rw) Then
            'select the B cell on the sheet
            Sheets("Customer").Range("B" & .List(rw, 7)).Select
            'exit loop
            Exit For
        End If
    Next rw
End With

'Unload Me

End Sub
 
Upvote 0
you could use an eighth column in the listbox to hold the additem row number
VBA Code:
SResults.List(RowCount, 7) = FirstCell(1, 15).row
then use that for the row to goto upon selection in the listbox
VBA Code:
Private Sub SResults_Click()
    Dim rw As Long
   
With Me.SResults
    'loop through listbox rows until at the row selected
    For rw = 0 To .ListCount - 1
        If .Selected(rw) Then
            'select the B cell on the sheet
            Sheets("Customer").Range("B" & .List(rw, 7)).Select
            'exit loop
            Exit For
        End If
    Next rw
End With

'Unload Me

End Sub
Absolutely fantastic! Thank you NoSparks, really appreciate your time and it works just perfectly - I can't see why I didn't just add the 8th column as you suggested, another pair of eyes/ideas and all that - seems simple now that you've given me the method.
Thanks again, amazing work!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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
Back
Top