Userform listbox to show values from a range

Question

I'm trying to create a Userform in Excel, where I have one ComboBox and based on the value chosen, values from a range of cells are to be shown in a listbox on the Userform.

So far I have this.

Private Sub UserForm_Initialize()

With ComboBox1()
   .AddItem "Item1"
   .AddItem "Item2"
   .AddItem "Item3"
   .AddItem "Item4"
   .AddItem "Item5"
   .AddItem "Item6"
   .AddItem "Item7"
End With

End Sub


Sub ComboBox1_Change()

    If ComboBox1.ListIndex = Outlook Then
        Range("A3:B11").Show
    Else
        If ComboBox1.ListIndex = NoNetwork Then
            Range("C3:D11").Show
        End If
    End If

End Sub

If I change Range("A3:B11").Show to Range("A3:B11").Select it will select this range.

How do I instead show the data from this range on the UserForm?

Where on the form is the data getting populated to? A listbox or something? — tigeravatar
Range.Show just scrolls the window, in short, it behaves like goto that cell. You don't show anywhere in your example where you try and load the contents of your range into a textbox, listbox another combobox ect on your form. If you are loading it into a listbox combobox then just loop though the cells and .AddItem CellValue — Sorceri

CallumDA CallumDA · Accepted Answer · 2018-03-07T18:29:05

Here's one way to do it:

1. Add some named ranges to your worksheet

The range B2:C3 has been assigned to a named range called Name1. The range E2:F3 assigned to Name2, etc.

2. Create a userform with a ComboBox and a ListBox

3. Place the following code in the relevant modules:

Userform1 module

Private Sub UserForm_Initialize()
    ComboBox1.List = Array("Name1", "Name2", "Name3", "Name4")
End Sub

Private Sub ComboBox1_Change()
    Dim n As Name
    Set n = ThisWorkbook.Names(ComboBox1.Value)

    If Not n Is Nothing Then
        ListBox1.RowSource = n.RefersToRange.Address
        ListBox1.ColumnCount = n.RefersToRange.Columns.Count
    End If
End Sub

Any standard module

Public Sub ShowUserform()
    With New UserForm1
        .Show vbModal
    End With
End Sub

4. Run the ShowUserform sub from the standard module and select a name!

Userform listbox to show values from a range

1 Answers