CODEWITHSUNDEEP
excelvba
Ilan
Ilan

Reputation: 162

How to assign a reference to a dynamically created object using Excel VBA

There are some answers to similar questions, but none of them work for me. Most of them are geared towards Forms whereas I am trying to add objects directly onto Excel worksheets. Beyond that, nearly all are incredibly complicated solutions for something that should not be so difficult.

Here is the straightforward kind of thing I'm looking for - but I need it for ToggleButton and ComboBox. The code below works for CommandButton but it seems to have no analog for the other ActiveX object types, so far as I could tell.

Sub AddButtons()
    Dim ws As Excel.Worksheet
    Dim btn As Button

    For Each ws In ThisWorkbook.Worksheets
        Set btn = ws.Buttons.Add(10, 20, 30, 40)
        Debug.Print ws.Name
        ' MySub_[worksheet name] will be executed when btn is clicked
        btn.OnAction = "MySub_" & ws.Name
        btn.Caption = ws.Name
        ' Set additional btn properties as needed
    Next ws
End Sub

(I'm not actually trying to create an object for each worksheet, but this example shows how one can loop through some construct and tether customized code to the object created within each iteration.)

Thanks!

Upvotes: 2

Views: 329

Answers (1)

Gro
Gro

Reputation: 1683

Activex controls are OLEObjects. I have shown how to add checkbox below.

Sub AddButtons()
    Dim ws As Worksheet
    Dim btn As OLEObject
    Debug.Print ("Start")
    For Each ws In ThisWorkbook.Worksheets
        Set btn = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=True, _
        DisplayAsIcon:=False, left:=10, top:=10, width:=20, _
        height:=20)
        Debug.Print (ws.Name)
    Next ws
End Sub

Upvotes: 1

Related Questions