Excel VBA: How do I refer to a Form Control, on a worksheet?

Question

I've added two Dropdown (aka ComboBox) to a Sheet

Starting from this question (How do I refer to a controls object, on a worksheet, using a variable name?) I tried the following experiment without succeding.

How do I access to the controls on an Excel Sheet? And where do I see the name / properties of the controls I have just inserted?

Answer 1

You can do something like this:

Sub DropDown1_Change()

    Dim s As Object
    Set s = ActiveSheet.Shapes(Application.Caller)
    Debug.Print s.ControlFormat.Value

End Sub

Application.Caller contains the name of the shape "containing" the form control

Similarly you can access other controls by name:

Dim myName as String, c As Object
myName = "List Box 2"
Set c = ActiveSheet.Shapes(myName).ControlFormat

Answer 2

Instead of adding it like that I suggest creating a Shape variable, and use that to add data/properties.

Something like this:

Sub t()
Dim newDD As Shape

Set newDD = ActiveSheet.Shapes.AddFormControl(xlDropDown, Left:=Cells(1, 1).Left, Top:=Cells(2, 1).Top, Width:=100, Height:=20)
 With newDD
        .ControlFormat.DropDownLines = 2
        .ControlFormat.AddItem "Item 1", 1
        .ControlFormat.AddItem "item 2", 2
        .Name = "New Combo Box"
        .OnAction = "myCombo_Change"
 End With

End Sub