Creating a dynamic button in a dynamically created sheet

Question

I have dynamically created a new sheet and 2 new buttons within this sheet (not on a userform), how do I call the click event for these two new buttons?

I only need 2 buttons and I know the name for them, however I cannot place the button_click code in Microsoft Excel Object as it is created dynamically. I have tried creating a Class Module however I get a prompt that the object does not support automation.

Can anyone help?

Set ws = ThisWorkbook.Sheets.Add
ws.Name = TableName

ws.Activate

 Set UploadButton = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)

  With UploadButton

    .Name = "UploadButton"
    .Object.Caption = "Upload"
    .Top = 0
    .Left = 0
    .Width = 100
    .Height = 20
    .Placement = xlMoveAndSize
    .PrintObject = True            'or false as per your taste

End With


Set CancelButton = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)

With CancelButton

    .Name = "CancelButton"
    .Object.Caption = "Cancel"
    .Top = 0
    .Left = 120
    .Width = 100
    .Height = 20
    .Placement = xlMoveAndSize
    .PrintObject = True            'or false as per your taste
End With

And I want to run this

    Private Sub UploadButton_Click()
    MsgBox "Co-Cooo!"
End Sub

Possible duplicate of: [Assign code to a button created dynamically](https://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically) — Pᴇʜ, Jun 18 '19 at 08:02

Creating a dynamic button in a dynamically created sheet

0 Answers0