Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

How to link two List Boxes in Excel

I need to link two list boxes in Excel.  The end result I'm trying to find is that when the user makes a selection in the first list box (lstAvailable) the second list box(lstAv_ID) corresponding values are selected as well

Any help would be appreciated!
0
RyanVT
Asked:
RyanVT
  • 6
  • 4
  • 2
1 Solution
 
Antagony1960Commented:
If the listboxes are filled with the same number of items and the ordinal position of the connected items is the same (i.e. Item1 = ID1, Item2 = ID2 ... etc.) then it's a simple matter of matching the ListIndex properties like this:

Private Sub lstAvailable_Click()
    lstAv_ID.ListIndex = lstAvailable.ListIndex
End Sub

0
 
RyanVTAuthor Commented:
Thanks for the response.  I probably need to include more details, I apologize as this is my first post to this site.  Let me give a little more detail about the problem I am working on.

I have two list boxes that are populated from an access database when the workbook is opened.  The first list box(lstAvailable) has customer names and the second list box has the corresponding customer ID's (lstAv_ID).

When the user selects names from lstAvailable then hits a command button those names move to a third list box (lstSelected) so I also need the ID's to move to a fourth list box (lstSe_ID) when that command button is used.  As well as being able to send them back to lstAvailable, lstAv_ID with a separate cmdButton

Thanks for the first solution, it definitely did as I first had asked for!

Private Sub cmdSelect_Click()
    SwitchLists lstAvailable, lstSelected
End Sub
 
Private Sub cmdUnselect_Click()
     SwitchLists lstSelected, lstAvailable
End Sub
 
Private Sub SwitchLists(ByRef lstFrom As Object, ByRef lstTo As Object)
    Dim i As Integer
    Dim intPosition As Integer
        
    'This function moves selected items on 'lstFrom' to 'lstTo'
    
    With lstFrom
        ' First add selected items to lstTo & remove from lstFrom
        For i = .ListCount - 1 To 0 Step -1  ' .ListCount = the # of items on a ListBox
         
            If .Selected(i) Then        ' .Selected(i) = True if item i is selected
                intPosition = Position(.List(i), lstTo)
                lstTo.AddItem .List(i), intPosition
                .RemoveItem i
            End If
        Next i
    End With
        
    Me.Cells(13, 4).Select
    EnableButtons
End Sub
 
Private Function Position(item As String, lstTo As Object) As Integer
    Dim i As Integer
    
    ' This function returns as integer indicating the position in 'lstTo'
    ' where 'item' should be inserted to maintain alphabetical order
    
    For i = 0 To lstTo.ListCount - 1
        If item < lstTo.List(i) Then
            Position = i
            Exit Function
        End If
    Next
    Position = lstTo.ListCount
End Function

Open in new window

0
 
thejzearfossCommented:
Another, perhaps easier option is to do a VLOOKUP.
Basically, you create a lookup table on a second worksheet, name a range for your lookup criteria, and use data validation to create a dropdown box in the cell on your main worksheet. You then put a VLOOKUP formula into the second cell that you're trying to populate, that will find the information in the master lookup table and supply the matching data.

It's illustrated in the attached sheet. Let me know if you have questions.
RyanVT.xls
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
thejzearfossCommented:
Sorry, i missed that you had added a comment when I was typing my response out -- it's def not what you're looking for -- sorry. I'll play with the problem as you describe it, although my vbasic isn't so hot right now -- i'm out of practice.
0
 
Antagony1960Commented:
Right, if I understand you correctly you want to use the secondary (ID) list boxes as matching place-holders for the IDs linked to the visible item list boxes, so that you can track an item's ID. If so, I think you may have missed a fairly obvious way of accomplishing what you need without introducing another set of list boxes; and that is to include the ID in the visible item list boxes, but in a hidden column. Add this code to the UserForm_Initialize event

Private Sub UserForm_Initialize()
Const iColWidth As Integer = 30 'Set this to the desired width of your column
    lstAvailable.ColumnCount = 2
    lstAvailable.ColumnWidths = iColWidth & ";0"
    lstSelected.ColumnCount = 2
    lstSelected.ColumnWidths = iColWidth & ";0"
End Sub

And put this line in your SwitchLists sub, directly underneath the AddItem line

                lstTo.Column(1, intPosition) = .Column(1, i)

Now, you can reference an items ID by the box's ListIndex value, like this:

    MyIDVar = lstSelected.Column(1, lstSelected.ListIndex)

Or, if you want to get a comma-delimited string listing the selected IDs use a function like this:

Private Function GetSelectedIDs() As String
Dim i As Integer, s As String
    With lstSelected
        If .ListCount > 0 Then
            For i = 0 To .ListCount - 1
                s = s & .Column(1, i)
                If i < .ListCount - 1 Then s = s & ","
            Next
            GetSelectedIDs = s
        End If
    End With
End Function
       
0
 
Antagony1960Commented:
Oh and I should have said: you'll need to set the hidden column in lstAvailable to the ID as you populate it from the data recordset, e.g. lstAvailable.Column(1, ItemNumber) = Item_ID

Let me know if you need any help with it.
0
 
RyanVTAuthor Commented:
How do I get the the two columns to populate from a database?

Public Sub InitializeLists()
    Dim Path As String
 
    Path = ThisWorkbook.Path
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    
    Set db = OpenDatabase(Path & "HokieStore.mdb")
    sql = "Select * From Customers Order By LName"
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    
    ' Place names on main listbox
    With Sheets("Selections").lstAvailable
        .Clear   ' Removes all items (if any) in the listbox
        While Not rs.EOF
            .AddItem rs("LName").Value & ", " & rs("FName").Value
    
            rs.MoveNext
        Wend
    End With
 
    With Sheets("Selections")
        .lstSelected.Clear
        .EnableButtons
        .Select
        .Cells(8, 3).Select
    End With
    
    db.Close
    Set db = Nothing   ' Frees space used by object variables
    Set rs = Nothing
End Sub

Open in new window

0
 
Antagony1960Commented:
Put Dim i As Integer at the top of the sub and place these two lines directly below the AddItem line, replacing "ID" with the name of your customer ID field:
            .Column(1, i) = rs("ID").Value
            i = i + 1

Open in new window

0
 
Antagony1960Commented:
Rich text screwed up that last post badly. Here's the code again:

            .Column(1, i) = rs("ID").Value
            i = i + 1
0
 
RyanVTAuthor Commented:
Thanks!

How would I then hide that second column?  So that the Data is all there but to the user only the Names are visible.
0
 
Antagony1960Commented:
That's done by setting the ColumnWidths property in the UserForm_Initialize event, as I posted earlier:

Private Sub UserForm_Initialize()
Const iColWidth As Integer = 30 'Set this to the desired width of your column
        lstAvailable.ColumnCount = 2
        lstAvailable.ColumnWidths = iColWidth & ";0"        lstSelected.ColumnCount = 2
        lstSelected.ColumnWidths = iColWidth & ";0"End Sub

0
 
RyanVTAuthor Commented:
Thank you very much for you help!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now