使用 VBA 设置组合框的值

Set value of combobox with VBA

提问by DSG

I have a combo box that contains 200+ values from a named range and the user can choose to eitherchoose any of these 200+ values orfreely type in any other value into the combo box. This works fine.

我有一个包含从命名范围200+值，并且用户可以选择组合框要么选择其中的任何200+值或任何其他值在组合框中键入自如。这工作正常。

My problem is that I am unable to fill in the same combo box with a value specified in a string variable in my VBA code. I want to do the following:

我的问题是我无法用 VBA 代码中的字符串变量中指定的值填充同一个组合框。我想做以下事情：

Sub FillInComboBox()
Dim strExample as String

strExample = "Random Text"

Worksheets("Sheet1").Shapes("ComboBox1").Value = strExample 

End Sub

I get the "Run-time error '438: Object doesn't support this property or method". I have also tried a lot of variations of the code above and I have googled for two hours without success, so I now turn to you as my last hope to get this working.

我收到“运行时错误 '438：对象不支持此属性或方法”。我也尝试了上面代码的很多变体，我已经用谷歌搜索了两个小时没有成功，所以我现在求助于你，这是我让这个工作正常的最后希望。

回答by Harun24HR

Use following sub.

使用以下子。

    Sub FillInComboBox()
    Dim strExample As String
        strExample = "Random Text"

        With Sheet1.ComboBox1
            .AddItem strExample
            .AddItem "Second Item"
            .AddItem "Third Item"
        End With
    End Sub

回答by PeterT

Looks like you're using an ActiveX ComboBox. There are a couple ways to accomplish what you want.

看起来您正在使用 ActiveX ComboBox。有几种方法可以完成你想要的。

1. Set the LinkedCellproperty of the ComboBox to your desired location (even on a hidden worksheet).

   • You can read the value of the combobox from that cell, even manually entered text
     Worksheets("Sheet1").Shapes("ComboBox1").LinkedCell = "C1"
   • You can set the value of the cell and it will be reflected in the combobox
     boxValue = Range("C1")
Range("C1") = "custom entry"

2. Access the ActiveX control objectdirectly.

   • I emphasized "object" because the control is a child object of the worksheet object. So you can do this:
     msgbox Worksheets("Sheet1").ComboBox1.Value
     and it gets you the value directly. Remember to use the name of the control you set in the Nameproperty of the control.
   • Setting the value works the same way:
     Worksheets("Sheet1").ComboBox1.Value = strExample

1. 将LinkedCellComboBox的属性设置为您想要的位置（即使在隐藏的工作表上）。

   • 您可以从该单元格中读取组合框的值，甚至可以手动输入文本
     Worksheets("Sheet1").Shapes("ComboBox1").LinkedCell = "C1"
   • 您可以设置单元格的值，它将反映在组合框中
     boxValue = Range("C1")
Range("C1") = "custom entry"

2. 直接访问 ActiveX 控件对象。

   • 我强调“对象”是因为控件是工作表对象的子对象。所以你可以这样做：
     msgbox Worksheets("Sheet1").ComboBox1.Value
     它直接为你提供价值。请记住使用您在控件的Name属性中设置的控件的名称。
   • 设置值的工作方式相同：
     Worksheets("Sheet1").ComboBox1.Value = strExample

Either way will gain access to the control data.

无论哪种方式都可以访问控制数据。

回答by DSG

I solved it myself:

我自己解决了：

Worksheets("Sheet1").OLEObjects("ComboBox1").Object.Value = strExample