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?

score 5 · Accepted Answer · answered Oct 24 '18 at 16:28

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

score 3 · Answer 2 · answered Oct 24 '18 at 16:29

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

This is something similar to the code behind in Visual Studio? — Revious, Oct 24 '18 at 21:14

score 1 · Answer 3 · answered Dec 25 '20 at 16:46

1

Try

Dim checkBox1 As Object
Set checkBox1 = Sheet1.OLEObjects("CheckBox1").Object
MsgBox checkBox1.Value

answered Dec 25 '20 at 16:46

LeslieM

2,105
1
17
8

1

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. [From Review](/review/low-quality-posts/27931341) – double-beep Dec 25 '20 at 17:47

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

3 Answers3