用户表单列表框显示范围内的值 [英] Userform listbox to show values from a range

问题描述

我正在尝试在Excel中创建一个用户窗体，其中有一个ComboBox，根据所选的值，应将一系列单元格中的值显示在用户窗体的列表框中.

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

如果将Range("A3:B11").Show更改为Range("A3:B11").Select，它将选择此范围.

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

我该如何在UserForm上显示此范围内的数据?

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

推荐答案

这里是一种方法:

1.将一些命名范围添加到您的工作表中

范围B2:C3已分配给名为Name1的命名范围.分配给Name2的范围E2:F3，等等.

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

2.用ComboBox和ListBox

2. Create a userform with a ComboBox and a ListBox

3.将以下代码放在相关模块中:

Userform1模块

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

任何标准模块

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

---

4.运行标准模块中的ShowUserform子菜单，然后选择一个名称！

---

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

这篇关于用户表单列表框显示范围内的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！