excel - 如何填充组合框

Question

我对 VBA 很陌生，并且一直在努力填充组合框。我正在尝试使用电子表格中第一列的内容填充组合框，以便我可以根据组合框选择删除关联的数据行。

在提出这个问题时，我已经浏览了这里和其他地方的几个问题，但我还没有发现任何有效的东西。

• populate combobox in VBA with array elements
• How do I populate a combo box from a column in my excel spread sheet?
• http://www.techrepublic.com/blog/microsoft-office/populate-a-userform-combo-box-in-excel-with-a-dynamic-list/

下面是我尝试过的代码。我有点迷失了，因为我一直试图将其他问题的不同答案拼凑在一起，以便使其发挥作用，但无济于事。我希望组合框填充第 1 列中的值，但它仍然是空白。

尝试#1 这涉及创建动态范围:

=OFFSET(PC_DataSheet!$A$2,0,0, COUNTA(PC_DataSheet!$A$1:$A$65536)-1,1)

Private Sub UserForm1_Initialize()

    Dim rngPCNumber As Range
    Dim ws As Worksheet

    Set ws = Worksheets("Sheet1")

    For Each rngPCNumber In ws.Range("PCNumber")
        Me.PC_ListComboBox.AddItem rngPCNumber.Value
    Next rngPCNumber

End Sub

尝试#2

Private Sub UserForm1_Initialize()

    Dim arr() As Variant

    arr = Worksheets("Sheet1").Range("C2:" & lrow).Value
    PC_ListComboBox.List = arr

End Sub

尝试#3

Private Sub UserForm1_Initialize()

    Dim vArr As Variant
    Dim i As Integer

    vArr = Sheet1.Range("A:1").Value

    With PC_ListComboBox.Clear
         For i = LBound(vArr) To UBound(vArr)
            .AddItem vArr(i)
         Next i
    End With

End Sub

对此的任何帮助将不胜感激!

<小时/>

编辑:我尝试将Gary's Student建议的代码插入到我的 UserForm_Initialize() Sub 中，但是当我尝试打开用户表单时出现以下错误消息:

Run-time error '9': Subscript out of range

当我单击“调试”时，它会突出显示此代码:

'Opens PC UserForm when pressed.
Private Sub AddPCButton_Click()

    UserForm.Show 'This line is the line highlighted by the debugger.

End Sub

我不知道是什么原因造成的...当我使用建议的代码时，我收到一条错误消息，但是当我删除代码时，用户表单可以完美运行。这是带有和不带有建议代码的 Private Sub UserForm_Initialize()。

'Clears and Initializes the form when first loaded.
Private Sub UserForm_Initialize()

    'Empties combo boxes.
    PC_OSTypeComboBox = ""
    PC_HDTypeComboBox = ""

    'Populates combo boxes.
    With PC_OSTypeComboBox
        .Clear
        .AddItem "Windows 8"
        .AddItem "Windows 7"
        .AddItem "Windows Vista"
        .AddItem "Windows XP"
        .AddItem "Windows 2000"
        .AddItem "Windows 98"
        .AddItem "Windows NT"
        .AddItem "Windows 95"
    End With
    With PC_HDTypeComboBox
        .Clear
        .AddItem "SATA"
        .AddItem "IDE"
        .AddItem "SCSI"
    End With

End Sub

这包括建议的代码:

'Clears and Initializes the form when first loaded.
Private Sub UserForm_Initialize()

    Dim N As Long, i As Long
    With Sheets("Sheet1")
        N = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    With PC_NumberComboBox
        .Clear
        For i = 1 To N
            .AddItem Sheets("Sheet1").Cells(i, 1).Value
        Next i
    End With

    'Empties combo boxes.
    PC_OSTypeComboBox = ""
    PC_HDTypeComboBox = ""

    'Populates combo boxes.
    With PC_OSTypeComboBox
        .Clear
        .AddItem "Windows 8"
        .AddItem "Windows 7"
        .AddItem "Windows Vista"
        .AddItem "Windows XP"
        .AddItem "Windows 2000"
        .AddItem "Windows 98"
        .AddItem "Windows NT"
        .AddItem "Windows 95"
    End With
    With PC_HDTypeComboBox
        .Clear
        .AddItem "SATA"
        .AddItem "IDE"
        .AddItem "SCSI"
    End With

End Sub

Answer 1

这是一个创建和填充表单样式组合框的 super 简单示例:

Sub FormsStyleComboBox()
    ActiveSheet.DropDowns.Add(411, 14.25, 124.5, 188.25).Select
    N = Cells(Rows.Count, "A").End(xlUp).Row
    strng = Range("A1:A" & N).Address
    Selection.ListFillRange = strng
End Sub

例如:

编辑#1

我创建了一个名为 Demo 的用户窗体，其中包含一个名为 MyBox 的组合框

在标准模块中我放置:

Sub DisplayIt()
    Demo.Show
End Sub

以及用户表单代码区域:

Private Sub UserForm_Initialize()
    Dim N As Long, i As Long
    With Sheets("Sheet1")
        N = .Cells(Rows.Count, 1).End(xlUp).Row
    End With

    With MyBox
        .Clear
        For i = 1 To N
            .AddItem Sheets("Sheet1").Cells(i, 1).Value
        Next i
    End With
End Sub

运行DisplayIt()会产生:

这是基于this tutorial