如何将Worksheet类型变量转为Sheet1类型实例并操作ActiveX控件？

问题背景 #

有8个工作表，每个表包含多个ActiveX组合框，部分组合框可过滤其他组合框的选项列表。尝试在独立标准模块中编写通用代码时，遇到如下类型错误：

Dim ws As Worksheet
Set ws = Sheet1

' 报错：无法从Worksheet类型对象获取Sheet1的成员
ws.Combobox1.List = arr

纠结是否需要在每个工作表模块单独编写处理程序，或是存在通用技巧可在任意模块中操作任意工作表的控件，当前倾向使用Select Case硬编码工作表名称（如Sheet1.Combobox1.List = arr）。

解决方案 #

1. 通过OLEObjects集合通用访问控件 #

Worksheet对象的OLEObjects集合可以直接定位ActiveX控件，不受工作表类型限制，是通用代码的最优选择：

Dim ws As Worksheet
Set ws = Sheet1

' 通过控件名称访问并赋值，控件名称在开发工具属性窗口中查看
ws.OLEObjects("Combobox1").Object.List = arr

• 只需确保传入正确的工作表对象和控件名称，即可在任意标准模块中操作任意工作表的ActiveX组合框。

2. 直接使用工作表代码名 #

VBA编辑器中工程窗口显示的工作表代码名（如Sheet1、Sheet2）是直接可访问的对象，无需声明为Worksheet类型，避免类型匹配问题：

' 直接用代码名访问控件，无类型报错
Sheet1.Combobox1.List = arr

如果需要通用处理，可结合CallByName实现动态调用：

Sub UpdateCombobox(targetWs As Object, cbName As String, arr As Variant)
    ' 动态设置组合框的List属性
    CallByName targetWs, cbName, VbSet, arr
End Sub

调用示例：

UpdateCombobox Sheet1, "Combobox1", arr

3. 通用事件处理逻辑 #

无需在每个工作表模块重复编写事件代码，可在标准模块中编写通用逻辑，然后在工作表模块的控件事件中调用：

• 工作表模块中（示例为Sheet1）：

Private Sub Combobox1_Change()
    ' 调用标准模块的通用过滤逻辑，传入当前工作表和触发控件名
    CommonFilter Me, "Combobox1"
End Sub

• 标准模块中：

Sub CommonFilter(ws As Worksheet, triggerCbName As String)
    ' 获取触发控件对象
    Dim triggerCb As Object
    Set triggerCb = ws.OLEObjects(triggerCbName).Object
    
    ' 示例：根据触发控件的值过滤Combobox2的选项
    Dim filteredList As Variant
    filteredList = GetFilteredOptions(triggerCb.Value) ' 自行实现过滤逻辑
    
    ' 赋值给目标组合框
    ws.OLEObjects("Combobox2").Object.List = filteredList
End Sub

' 示例过滤函数，根据输入返回过滤后的列表
Function GetFilteredOptions(filterVal As String) As Variant
    ' 这里编写具体的过滤逻辑，比如从数据源中筛选符合条件的项
    GetFilteredOptions = Array("选项1", "选项2") ' 仅为示例
End Function

内容的提问来源于stack exchange，提问作者Alx795