Select multiple data validation objects respectively

[MeiR_ct]

Hi all,
I have a VBA function that performs a search in another sheet, and returns bunch of found row numbers, in a collection.
Now, in my procedure, the code loops on the results and creates 3 comma separated lists, based on info from the rows.

    For i = 1 To FoundRows.Count
        rownum = CLng(FoundRows(i))
        With Sheets(SheetToSerach)
            resID = resID & ", " & .Cells(rownum, "A").Value
            resName = resName & ", " & .Cells(rownum, "E").Value
            resAddr = resAddr & ", " & .Cells(rownum, "F").Value
        End With
    Next i
    'Remove first comma+space
    resID = Mid(resID, 3)
    resName = Mid(resName, 3)
    resAddr = Mid(resAddr, 3)

Afterwards, these 3 lists are added to a 3 cells in the handled row, as validation data lists.

    With Cell(r, IDCol).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=resID
    End With
    With Cell(r, NameCol).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=resName
    End With
    With Cell(r, AddrCol).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=resAddr
    End With

Now, I want that when some object in one of these lists is selceted, the 2 other lists will change to same object number respectively.
Here is an example of 3 cells, which have lists that contain 4 lines each:

--- D5: ---------- D6: ------------ D7: ---------
558097913 ______ John _______ John's Address
234957124 ______ Bill _______ Bill's Address
305053132 ______ Peter _______ Peter's Address
280894561 ______ Adam _______ Adam's Address

The idea is when a user selects "Bill" in D6's list, then D5 and D7 cells will change to Bill's ID and address. Same if user selects some ID, or some address.


Some other question: What is a best way I can "tie" the 3 lists together respectively always, so there won't be mismatch between details?
Maybe to store them in other manner?
Having them as plain sheet data is not acceptable, since there are thousands of searches, which means thousands of result lists.


Thanks a lot in advance!
Meir.

 

[Damon Ostrander]

Hi Meir,

While it is possible (but not simple) to link the validation lists, have you considered using multi-column in-cell listboxes (ListBox controls) instead? You can approximate the functionality of cell validation lists by using a Worksheet_SelectionChange event to pop up the appropriate listbox by making it visible.

Damon

 

[MeiR_ct]

To be honest, I have seen some posts that suggesting ListBox\ComboBox rather than data validation drop down list.

Can you please help me with an event code and storing loops that will suit my needs as I detailed above?

Thanks in advance, Meir

 

[Damon Ostrander]

Hi Meir,

Take a look at this example and let me know if this is what you have in mind:

http://www.filefreak.com/files/8072....xls]Multi-Col Listbox cell dropdown list.xls

Damon