Populating ActiveX comboboxes with distinct values

Question

My issue is that I want to populate an ActiveX control combobox with a specific name, List_Funds as I renamed it. The population is based on a specific column in a table I have generated in my workbook called Table_Funds. I want the combobox to only populate the unique values of the table. The code should run when I open the workbook.

Code below with my current attempt:

Below the code is in a designated module containing all my declarations

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

Below the code is in ThisWorkbook module

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

My code fails at the line If Not .exists(rng.Value) Then giving me

run-time error 438 'Object does not support this property or method.'

The table is populating as it should (i.e. you can disregard the section populating the table as it calls on a sub in a different module) and watching the code I know that the rng takes on the correct value (first value in the databodyrange of my table).

Answer 1

It is possible that someone created a function. Try replacing this block:

' 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