如何在Excel 2016 VBA中引用ActiveX ComboBox [英] How to reference ActiveX ComboBox in Excel 2016 VBA

问题描述

我在这里和其他地方都尝试过各种解决方案，例如:

I've tried various solutions on here and elsewhere such as:

dim wb as workbook, ws as worksheet

---

Dim cb As Object
Set cb = ws.OLEObjects("ComboBoxViews")

或

ComboBoxViews

或

ws.comboboxviews

但是全部返回错误1004，未找到具有指定名称的项目.但是它确实存在，检查属性的名称很明显是'comboboxviews'.

But all return Error 1004, the item with specified name wasn't found. But it DOES exist, checking properties the name is very clearly 'comboboxviews'.

有什么想法吗?

为了让将来寻求帮助并使用Romcel的非常有用的代码作为基础的其他人清楚，看来要向ActiveX ComboBox添加项目(这是我的最终目标)，您需要引用它作为对象:

To make it clear for anyone else seeking help in the future and using Romcel's very helpful code as a base, it seems that in order to add items to an ActiveX ComboBox (which is my final goal), you need to reference it as an object:

Sub caller3()
Dim ws As Worksheet
Dim oleob As OLEObject
Set ws = ThisWorkbook.Sheets("Sheet1")

For Each oleob In ws.OLEObjects
  If TypeName(oleob.Object) = "ComboBox" Then
    oleOb.Object.AddItem "TEST"  <<< CORRECT
    oleOb.AddItem "TEST"  <<< INCORRECT
  End If
Next

End Sub

推荐答案

您可以像在工作表中那样直截了当地引用控件.

You can bluntly refer to your controls in the worksheet like.

Sub caller1a()
Sheet1.ComboBox1.Value = "value 1a"    '   no errors
End Sub

但是您不能像这样引用它们.

But you cannot referto them like this.

Sub caller1b()
Dim ws As Worksheet
  Set ws = Sheet1
  ws.ComboBox1.Value = "value1b"    '  will give error
End Sub

如果您熟悉Excel如何为每个控件提供各自的索引.您可以将每个控件称为.

If you are familiar with how Excel gives each control their respective index. You can refer to each control as.

Sub caller2()
Dim ws As Worksheet
  Set ws = Sheet1
  ws.OLEObjects(1).Object.Value = "value2"
End Sub

或者只是安全地遍历它们，并检查它是否是您使用的正确控件.

Or just safely loop through them and check if it is the right control you are working with.

Sub caller3()
Dim ws As Worksheet
Dim oleob As OLEObject
Set ws = ThisWorkbook.Sheets("Sheet1")

For Each oleob In ws.OLEObjects
  If TypeName(oleob.Object) = "ComboBox" Then
    oleob.Object.Value = "value3"
  End If
Next

End Sub

希望这会有所帮助.祝你好运！

Hope this helps. Good luck!

这篇关于如何在Excel 2016 VBA中引用ActiveX ComboBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！