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?

Answer 1

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!