Trying to highlight/select a listbox value from another listbox.

[afrazier1221]

Here's what I'm doing, and what I'm trying to do.

Since my Microsoft Access skills suck, I'm creating a business database out of an Excel Workbook. For lack of a better way to describe it, I have nested data tables, and each functions interdependently with the other tables. In particular, I have four specific tables of data.

1) Customer Data — Each line of customer data begins with a customer index number, with the first record being 1 (like a key in Access).
2) Vehicle Data — Each line of vehicle data begins with a vehicle index number, with the first record being 1, followed by the customer index number to whom the vehicle belongs.
3) Estimates — Each line of estimate data begins with an estimate index number, with the first record being 1, followed by the customer index number to whom the estimate belongs, and then by the vehicle index number of that customer for which the estimate was made.
4) Invoices — Each line of invoice data begins with an invoice index number, with the first record being 1, followed by the customer index number to whom the invoice belongs, and then by the vehicle index number of that customer for which the invoice was made.

The user form contains four listboxes.
The first listbox is a list of customers in alphabetical order. When you manually click on a selection in this listbox, besides populating the relevant text boxes with the customer name, address, etc., xlookup functions are used on a separate worksheet to generate three lists, based on the customer index number, which is determined by the customer listbox selection. These xlookups use the customer index number to find vehicles in the vehicle database, estimates in the estimates database, and invoices in the invoices database that each belong to that specific customer. The other three listboxes are then populated by these three lists in turn, so that the practical result is that you select a customer from the customer list, and three other listboxes populate to show you, and make selectable, all the vehicles, active estimates, and invoices belonging to that customer.
The second listbox is for the list of vehicles. When you select a vehicle in the list, it populates the relevant text boxes with the year, make, model, etc.
When you select either an estimate or an invoice from their respective listboxes, it automatically updates the vehicle relative to the vehicle recorded in the invoice or estimate record. Say, for example, a customer has three vehicles. If I select an invoice from the invoice listbox, it uses the recorded vehicle index number to access the vehicle data, and populate the vehicle text boxes. If I select an estimate from the estimate listbox, it does the same. The relevance here is that the estimate or invoice was generated for a specific vehicle. So when I select an estimate or invoice, I want it to automatically load the related customer and vehicle information.

Up to this point, everything works like a charm. I have only one issue. I would like to have the vehicle listbox item highlighted/selected in addition to having the textboxes populated. So if I select a customer, and that customer has two vehicles, one open estimate, and three invoices, I want to be able to click on an invoice, have it populate the vehicle information boxes (which it already does), and select the relevant vehicle in the list so it shows which selection I'm on. It doesn't have to do this. What I've done works completely. But for the sake of aesthetics, how might I go about determining the correct vehicle listbox item, and then selecting it automatically via a click on a separate listbox?

I appreciate the feedback. The simpler the solution, the better.

 

[Akuini]

If they are single select listbox (frmMultiSelectSingle), maybe something like this:
Selecting listbox2 will affect selection on listbox1, the 1st column of listbox1 will match the 2nd column of listbox2.

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 2
ListBox2.ColumnCount = 2
ListBox1.List = Range("a1:b10").Value
ListBox2.List = Range("c1:d10").Value
End Sub

Private Sub ListBox2_Click()
ListBox1.Value = ListBox2.List(ListBox2.ListIndex, 1)
End Sub

In this example "Vehicle" is in col A & D.

Book1
	A	B	C	D
1	A	WQPC	John	E
2	B	TLVS	Jessica	X
3	E	SYLV	Joseph	W
4	F	SEKL	Barbara	Y
5	M	IWCF	Brian	A
6	N	YHDN	Christine	A
7	P	XUTQ	Ryan	A
8	W	EKWO	Stephanie	F
9	X	NRKA	Mark	M
10	Y	KNPQ	David	B
Sheet1

 

[afrazier1221]

Thanks Akuini. The issue was a little more dynamic than that. I did manage to get it figured out with a lot of hair pulling and cussing. I ended up having to create a list on a worksheet that contained the vehicle list for the given customer, and then used a VLOOKUP to determine the index number it should be in the list. Then I used:

If Sheet7.Cells(3, 5).Value < 25 And Sheet7.Cells(1, 9) <> -1 Then
k = Sheet7.Cells(1, 9).Value
Me.CVLB.Selected(k) = True
End If

• CVLB is the name of my Customer Vehicle List Box.
• The If statement was checking that the list wasn't empty (an empty list would have 25 blank cells), which was a fix for a different routine that was glitching when it tried to determine the index number when the listbox was empty. The If statement was also checking to make sure the VLOOKUP wasn't trying to find the "unselected" option -1, which wouldn't appear in the list.
• Sheet7.Cells(1, 9).Value is the worksheet cell where the calculated listbox index number is found.

Thanks for your time in trying to help me resolve the issue.