CODEWITHSUNDEEP
excelvba
Revious
Revious

Reputation: 8146

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

I've added two Dropdown (aka ComboBox) to a Sheet enter image description here

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.

enter image description here

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?

Upvotes: 4

Views: 11253

Answers (3)

LeslieM
LeslieM

Reputation: 2303

Try

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

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

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

Upvotes: 3

Tim Williams
Tim Williams

Reputation: 166196

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

Upvotes: 6

Related Questions