Announcement

Collapse
No announcement yet.

Linking combo box to list box on userform

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Linking combo box to list box on userform



    Hi all, very much of a newbie to the VBA language (doesn't seem Basic to me!) and this forum, so be gentle pls.

    I suspect this is everyday easy stuff for you folks so here goes........

    I am trying to create a userform that has a combobox linked to a named range on a worksheet. When the user selects a city from the combo box a filtered list of all the clients in the city is displayed in a listbox below it. Writing the code to point the combobox to the named range sorted OK (thanks to a look through this forum). Just need a push in the right direction as to how to take the value from the combobox and apply it to a listbox that returns only the relevant clients.

    Hope that makes sense.
    Many thanks in advance

    Bellyfunk

  • #2


    HI Bellyfunk

    Welcome to ozgrid.com

    See the attached example for one way. Code shown below and in the VBE of the Workbook.
    Code:
    Private Sub ComboBox1_Change()
    Dim strSheet As String, strAddress As String
    Dim iListIndex As Integer, strSheetName As String
    Dim rLastcell As Range, rFisrtCell As Range
    
        If ComboBox1.ListIndex > -1 Then
           iListIndex = ComboBox1.ListIndex + 2
           Set rFisrtCell = Sheet1.Cells(2, iListIndex)
           Set rLastcell = Sheet1.Cells(65536, iListIndex).End(xlUp)
           strAddress = Range(rFisrtCell, rLastcell).Address
           strSheetName = "'" & Sheet1.Name & "'!"
           ListBox1.RowSource = strSheetName & strAddress
        End If
    End Sub
    Attached Files

    Comment

    Working...
    X