VBA passing listbox to a function

Question

I am attempting to populate a listbox by passing my listbox object to a subfunction, but whenever I run it I get a type mismatch error. I pass the object MAIN.BoxY1 to the function FillListBox, where MAIN is the codename for the sheet the listbox is on, and BoxY1 is my listbox name (ActiveX). When I alter the FillListBox function to include MAIN.BoxY1 instead of MyBox for every instance it will work fine. What is the proper method of passing a listbox to another function?

Sub FillListBox(MyBox As ListBox, DataList As Variant)
    MyBox.MultiSelect = 1
    For j = 1 To NumOutputs
        MyBox.AddItem DataList(j)
    Next j
End Sub

Sub BoxY1_Fill()
    FillListBox MAIN.BoxY1, TheData
End Sub

 

Answer 1

There are two types of listbox in Excel: the "built-in" type and the ActiveX version. Depending on which type you are dealing with, you need to set up your function parameter differently:

Sub Testing()
    test1 Sheet1.ListBox1                            ' <<ActiveX Listbox
    test2 Sheet1.ListBoxes("ListBox2")               ' <<Forms Listbox
    test2 Sheet1.Shapes("ListBox2").OLEFormat.Object ' <<Forms Listbox (avoiding 
                                                     '   deprecated 'Listboxes')
End Sub

'ActiveX listbox
Function test1(lb As msforms.ListBox)
    Debug.Print lb.ListCount
End Function

'Forms listbox
Function test2(lb As ListBox)
    Debug.Print lb.ListCount
End Function