How do I get the name of the current Listbox in VBA?

Question

according to How to address generated ListBoxes and add Items dynamically in VBA? I generated several ListBoxes dynamically which I assigned a OnAction Sub to. The OnAction Event shall show me the amount of selected Items of the current ListBox.

For better understanding:

e.g. I generated 5 Listboxes (ListBox1, ListBox2, ...) on my Worksheet "FS".

When I click 1 item on ListBox1, MsgBox shows up with "1".

If I click an additional item on ListBox1, MsgBox shows "2".

But If i click another Item of ListBox3, MsgBox shall show "1".

    'ListBoxName as a variable for current ListBox and probably the most problematic line
    ListBoxName = ActiveControl.Name

    selectedItems = 0

    Set lb = FS.ListBoxes(ListBoxName)

    'The following counts the selected items in target ListBox
    For i = 1 To lb.ListCount Step 1
        If lb.Selected(i) Then
            selectedItems = selectedItems + 1
        End If
    Next i

    'This part puts up the Message Box with the number of selected items in target ListBox
    If selectedItems > 0 Then
        MsgBox selectedItems
    End If

Additional information: I left out the declarations of the variables for better overview. I am not using ActiveX and I am not using a UserForm. Getting the current name of the Listbox will help me with further programming and tasks I want to do. In my mind it looked so easy to find the name of the ListBox on which I did my latest click...

Thank you in advance!

EDIT: The code is placed in a module "Module1" in Sub Module1. In another Module "generation", where I generate the ListBoxes, the Module1 is started with lb.OnAction = "Module1.Module1".

EDIT2 Changed "OnClick" to "OnAction"

So these are Forms controls on the sheet and your code is a normal sub in a standard module? I'm not clear how your code is run. I don't think ActiveControl is right. Forms listboxes don't have a Click event. — SJR
Yes. The Form Controls are generated and their Properties assigned in Module "Generation". You are absolutely right, there is no "OnClick" Event but it seems, that there is a "OnAction" Event. I know that, because i programmed it with a "OnAction" Event and it worked. But only if i specify the ListBox. — DXR

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-07-10T11:21:36

You cannot use ActiveControl for Form listboxes. To get the name of the Form listbox which called the ONCLICK event, use Application.Caller property (Excel)

In the ONCLICK event of the forms listbox, put this.

Sub ONCLICK()
    Dim shp As Shape
    Set shp = Shapes(Application.Caller)

    '~~> This will give you the value of what is selected in that listbox
    MsgBox shp.ControlFormat.List(shp.ControlFormat.Value)
End Sub

Note: If the above code is not in the respective sheet code area then you will have to chnage Shapes(Application.Caller) to Activesheet.Shapes(Application.Caller) as Yasser mentioned below.

How do I get the name of the current Listbox in VBA?

1 Answers