使用不同的值填充 ActiveX 组合框

Question

我的问题是我想用特定名称填充ActiveX 控件组合框，List_Funds重命名它时。总体基于我在工作簿中生成的名为Table_Funds的表中的特定列。我希望组合框仅填充表的唯一值。当我打开工作簿时，代码应该运行。

下面的代码与我目前的尝试：

下面的代码位于包含我所有声明的指定模块中

Option Explicit
Option Base 0
' This module contains all constants and variable declarations
' **** Declarations ****
' Worksheets and workbooks
Public ws             As Worksheet
Public ws_O           As Worksheet
Public ws_S           As Worksheet
Public wkb            As Workbook
' Integers
Public i              As Integer
Public j              As Integer
' Variants, objects and ranges
Public Data           As Variant
Public Funds_List     As Object
Public rng            As Range
Public tbl            As ListObject
Sub Fixed()
Set wkb = ThisWorkbook
Set ws_O = wkb.Sheets("Overview")
Set ws_S = wkb.Sheets("SQL")
Set Funds_List = ws_O.OLEObjects("List_Funds").Object
Set tbl = ws_O.ListObjects("Table_Funds")
End Sub

下面的代码位于此工作簿模块中

Option Explicit
Private Sub Workbook_Open()
' Computing when opening workbook
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Call modCnt.Fixed
' Populating table
Data = modGlobal.GetSql(modGlobal.Compose_sSql(1))
tbl.DataBodyRange.ClearContents
For i = LBound(Data, 2) To UBound(Data, 2)
For j = LBound(Data, 1) To UBound(Data, 1)
tbl.DataBodyRange(1 + i, 1 + j) = Data(j, i)
Next j
Next i
' Populating combobox
With Funds_List
For Each rng In tbl.ListColumns("Name").DataBodyRange
If Not .exists(rng.Value) Then ' < ---- code fails here
.AddItem rng.Value
End If
Next rng
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

我的代码在行If Not .exists(rng.Value) Then失败 给我

运行时错误 438"对象不支持此属性或方法"。

该表正在按预期填充(即，当它调用不同模块中的 sub 时，您可以忽略填充表的部分)并观察代码我知道rng采用正确的值(我表的数据主体范围中的第一个值)。

Answer 1

可能是有人创建了一个函数。 尝试替换此块：

' Populating combobox
Dim Exists As Boolean
Dim t As Long
Exists = False
With Funds_List
For Each Rng In tbl.ListColumns("Name").DataBodyRange
For t = 1 To .ListCount - 1
If Rng.Value = CStr(.List(t)) Then
Exists = True
Exit For
End If
Next t
If Exists = False Then
.AddItem Rng.Value
End If
Next Rng
End With