Excel VBA Type Mismatch calling Private Sub from ListBox_Click

Question

I have a Sheet that I have put a ActiveX List Box onto, and a function which will update a certain ListBox (it's listfillrange is a named range with has dynamic size) All the following code is within the Microsoft Excel Objects - Sheet1

Function to update ListBox

Private Sub update(lst As ListBox)
    lst.ListIndex = -1
    lst.ListFillRange = lst.ListFillRange
End Sub

I want to call this function in two scenarios:

1. When the Worksheet changes:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect("{some ranges}") Is Nothing Then update Me.ListBox
End Sub

2. When a different ListBox is clicked on:

Private Sub OtherListBox_Click()
    update Me.ListBox
End Sub

However when the code tries to call the update sub it gives me a Type Mismatch error on the update line This is my first time using List Boxes, so my understanding is pretty limited and I can't understand why both scenarios won't work

Answer 1

A little bit confusing: If you are using ActiveX-Controls, you need to declare the parameter as MSForms.Listbox.

Private Sub update(lst As MSForms.ListBox)
    lst.ListIndex = -1
    lst.ListFillRange = lst.ListFillRange 
End Sub

ListBox (without the MSForms.) will refer to the "older" Listbox type (so called Form Controls).