以编程方式在VBA中添加ComboBox（Excel）

I am trying to create, place and fill in elements of a ComboBox in VBA. I don't understand what I am missing, but I cannot possibly figure out how to do it. I do not have MSForms in my API (i.e. I cannot write Dim someComboBox As MSForms.ComboBox as it fails. Is it possible to import it somehow? Can someone point me in the right direction?

我正在尝试在VBA中创建，放置和填充ComboBox的元素。我不明白我错过了什么，但我无法弄明白该怎么做。我的API中没有MSForms（即我无法将Dim someComboBox写为MSForms.ComboBox，因为它失败。是否有可能以某种方式导入它？有人能指出我正确的方向吗？

What I want to achieve is that when the user clicks a button a new form is created with various items (ComboBoxes, RadioButtons, etc.) and thus I want to do this programatically.

我想要实现的是，当用户单击一个按钮时，会创建一个包含各种项目（ComboBoxes，RadioButtons等）的新表单，因此我想以编程方式执行此操作。

2 个解决方案

#1

You can rely on the following code:

您可以依赖以下代码：

 Set curCombo = ActiveSheet.Shapes.AddFormControl(xlDropDown, Left:=Cells(1, 1).Left, Top:=Cells(2, 1).Top, Width:=100, Height:=20)
 With curCombo
        .ControlFormat.DropDownLines = 2
        .ControlFormat.AddItem "Item 1", 1
        .ControlFormat.AddItem "item 2", 2
        .Name = "myCombo"
        .OnAction = "myCombo_Change"
 End With

#2

You need a reference to Microsoft Forms 2.0 Object Library. An easy way to do this is to just insert a UserForm in your project.

您需要对Microsoft Forms 2.0对象库的引用。一种简单的方法是在项目中插入UserForm。

Alternatively, find this reference in the Tools/ References dialog.

或者，在“工具/参考”对话框中找到此参考。

You can programmatically create a UserForm, a ComboBox, etc., but it would be easier to just insert a UserForm and leave it hidden until needed. You can programmatically add new controls to it still, if needed.

您可以以编程方式创建UserForm，ComboBox等，但是只需插入UserForm并将其隐藏起来直到需要它会更容易。如果需要，您还可以以编程方式向其添加新控件。

Added The Forms Object Library won't enable you to create entirely new forms and controls. You need the Microsoft Visual Basic for Applications Extensibility library. This link is old, but still relevant.

添加表单对象库不会使您创建全新的表单和控件。您需要Microsoft Visual Basic for Applications Extensibility库。这个链接很旧，但仍然相关。

#1