vba 如何填充组合框

Question

提问by ckemmann

I'm quite new to VBA and I've been struggling with populating a combobox. I'm trying to fill a combobox with the contents of the first column in a spreadsheet so I can delete the associated row of data based on the combobox selection.

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

I've looked through several questions both here and elsewhere when making this question, but I haven't found anything that worked.

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

Below is the code I've tried. I'm somewhat lost as I've been trying to cobble together the different answers from other questions in order to get this to work, but to no avail. I expect the combobox to populate with the values from column 1, but it remains blank.

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

Attempt #1 This involved creating a dynamic range:

尝试 #1 这涉及创建一个动态范围：

=OFFSET(PC_DataSheet!$A,0,0, COUNTA(PC_DataSheet!$A:$A536)-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

Attempt #2

尝试#2

Private Sub UserForm1_Initialize()

    Dim arr() As Variant

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

End Sub

Attempt #3

尝试 #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

Any help on this would be really appreciated!

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

EDIT:I've tried inserting the code suggested by Gary's Studentinto my UserForm_Initialize() Sub, but when I try to open the userform I get the following error message:

编辑：我尝试将Gary 的学生建议的代码插入我的 UserForm_Initialize() Sub，但是当我尝试打开用户表单时，我收到以下错误消息：

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

运行时错误“9”：下标超出范围

When I click debug, it highlights this code:

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

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

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

End Sub

I have no idea what's causing this...when I use the suggested code, I get an error message, but when I remove the code the userform functions flawlessly. Here's Private Sub UserForm_Initialize() with and without the suggested code.

我不知道是什么原因造成的……当我使用建议的代码时，我收到一条错误消息，但是当我删除代码时，用户表单可以完美运行。这是带有和不带有建议代码的 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

This is including the suggested code:

这包括建议的代码：

'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

采纳答案by Gary's Student

Here is a super simple example of creating and filling a Formsstyle combo-box:

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

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

For example:

例如：

enter image description here

在此处输入图片说明

EDIT#1

编辑#1

I created a UserForm called Democontaining a combo-box called MyBox

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

enter image description here

在此处输入图片说明

In a Standard ModuleI put:

在标准模块中，我放了：

Sub DisplayIt()
    Demo.Show
End Sub

and in the UserForm code area:

并在用户窗体代码区域中：

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

Running DisplayIt()produces:

运行DisplayIt()产生：

enter image description here

在此处输入图片说明

This is based on this tutorial

这是基于本教程

Answer 2

回答by ckemmann

So I tried the solution Gary's Student suggested, which worked when I created a new workbook with the code he provided, but for some reason the 'subscript out of range'error kept coming up when I implemented it in my project, no matter what I did to rename my worksheets in the workbook, including setting up a sub to list all the worksheets and call the sheet from there.

所以我尝试了 Gary's Student 建议的解决方案，当我用他提供的代码创建一个新的工作簿时，它起作用了，但是由于某种原因，当我在我的项目中实现它时，“下标超出范围”错误不断出现，无论我做什么确实在工作簿中重命名了我的工作表，包括设置一个子列表以列出所有工作表并从那里调用工作表。

I opted instead to use an inputbox instead of a combobox, which ended up being a little more straightforward to code. Below is the code for anyone curious.

我选择使用输入框而不是组合框，这最终使编码更直接一些。以下是任何好奇的人的代码。

Private Sub DeletePCButton_Click()

'Assigns variables for delete sequence.
Dim PCNumberEntry As String
Dim Confirm As Integer
Dim r As Range
Dim c As Range
Dim cellsToDelete As Range
Dim m As Integer

'Asks for PC entry to be deleted.
PCNumberEntry = InputBox("Enter the number of the PC you wish to remove:", "Delete PC Entry", "PC 1", vbOKCancel)

'Closes inputbox when cancel is pressed.
If PCNumberEntry = "" Then
    Exit Sub
End If

'Searches worksheet column "A" and finds any existing PC entries.
Set r = Sheet1.Range("A:A")
For Each c In r

    'Checks for matching entry in worksheet to begin delete sequence.
    If (c.Value) = PCNumberEntry Then
        m = True

        'Asks for confirmation from user before deleting entry.
        Confirm = MsgBox("Warning! Once deleted, an entry cannot be restored! Only proceed if you are sure you wish to delete a row.", vbYesNo Or vbExclamation)

        'Cancels delete operation when "No" button is pressed.
        If Confirm = vbNo Then
            Exit Sub
        End If

        'Deletes entry and informs user of successful operation.
        If cellsToDelete Is Nothing Then
            Set cellsToDelete = c
            Call cellsToDelete.EntireRow.delete
            MsgBox ("The entry was deleted.")
        End If

    End If

Next c

    'Displays error message if no matching entry is found.
    If m = False Then
        MsgBox ("No entry with that number was found!")
    End If

On Error Resume Next

End Sub

结束子

Answer 3

回答by Kevin

Private Sub UserForm_Initialize()

    Dim CS As Integer
    Dim CR As Integer
    Dim RF As Integer
    Dim PW As Integer
    Dim CD As Integer

        CS = ActiveWorkbook.Sheets("LISTS").Columns(2).End(xlDown).Row      
        CR = ActiveWorkbook.Sheets("LISTS").Columns(3).End(xlDown).Row      
        RF = ActiveWorkbook.Sheets("LISTS").Columns(4).End(xlDown).Row      
        PW = ActiveWorkbook.Sheets("LISTS").Columns(5).End(xlDown).Row      
        CD = ActiveWorkbook.Sheets("LISTS").Columns(6).End(xlDown).Row      

    With CB_CS
        .Clear
        For i = 2 To CS + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 2).Value
        Next i
    End With

    With CB_CR
        .Clear
        For i = 2 To CR + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 3).Value
        Next i
    End With

    With CB_RF
        .Clear
        For i = 2 To RF + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 4).Value
        Next i
    End With

    With CB_PW
        .Clear
        For i = 2 To PW + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 5).Value
        Next i
    End With

    With CB_CD
        .Clear
        For i = 2 To CD + 1
            .AddItem ActiveWorkbook.Sheets("LISTS").Cells(i, 6).Value
        Next i
    End With
End Sub

The code above is located in my UserForm Code (Right-Click on UserForm, then click 'view code')

上面的代码位于我的用户窗体代码中（右键单击用户窗体，然后单击“查看代码”）

I created a Worksheet call LISTS. Each column on that sheet is for a different combo-box. Once I filled it out and got the code working I hid the LISTS worksheet.

我创建了一个工作表调用列表。该工作表上的每一列都用于不同的组合框。一旦我填写了它并使代码工作，我就隐藏了 LISTS 工作表。

Each ComboBox I named CB_XX so note those names in the code

我命名为 CB_XX 的每个 ComboBox 因此请注意代码中的这些名称

I start the code by defining the length of the lists (note this fails if you only have one item in the list but if you only have one item don't use a combo box)

我通过定义列表的长度来启动代码（请注意，如果列表中只有一项，则此操作将失败，但如果您只有一项，请不要使用组合框）

Once I get the lengths I add the correct columns to the correct comboboxes. Note the +1 in each for/next loop. That is to add a blank at the end of each list to allow the user to empty the selection. Remove the +1 if you don't want that blank. I start at i = 2 to not show the header row on my LISTS sheet.

获得长度后，我将正确的列添加到正确的组合框。注意每个 for/next 循环中的 +1。即在每个列表的末尾添加一个空格，以允许用户清空选择。如果您不想要那个空白，请删除 +1。我从 i = 2 开始，在我的 LISTS 表上不显示标题行。

vba 如何填充组合框

提问by ckemmann

采纳答案by Gary's Student

回答by ckemmann

回答by Kevin

相关推荐

最近更新

标签

vba 如何填充组合框

提问by ckemmann

采纳答案by Gary's Student

回答by ckemmann

回答by Kevin

相关推荐

Excel VBA 代码的密码保护如何工作？

VBA 中的 If 和 Or 多条语句

vba 识别基于文本的单元格中的无效字符

vba 在excel vba中查找`find`方法是否返回`nothing`

相关推荐

最近更新

标签