Handling Events for OLEObject CommandButtons created at Runtime

by

I have one rather impractical solution. To test it place the following code in Sheet Class Module (see attached image). The Me.CodeName refers to Code-Name of the sheet.

For each new Sheet1-button a new event handled will be added. This event handler will execute the common event handler and pass the name of the clicked command button to it.

' Standard Module
Sub test()
  ' adds three buttons to Sheet1 with click-event handlers
  Sheet1.AddButton
  ActiveCell.Offset(5, 0).Activate
  Sheet1.AddButton
  ActiveCell.Offset(5, 0).Activate
  Sheet1.AddButton
End Sub

' Sheet1 Class Module
Option Explicit

' Add Microsoft Visual Basic For Applications Extensibility

Public Function AddButton() As MSForms.CommandButton
  Dim msFormsCommandButton As MSForms.CommandButton
  Set msFormsCommandButton = Me.OLEObjects.Add(ClassType:="Forms.CommandButton.1").Object
  CreateEventHandler msFormsCommandButton.Name
  Set AddButton = msFormsCommandButton
End Function

Private Sub CommonButton_Click(ByVal buttonName As String)
  MsgBox "You clicked button [" & buttonName & "]"
End Sub

Private Sub CreateEventHandler(ByVal buttonName As String)
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim codeText As String
    Dim LineNum As Long
    
    Set VBComp = ThisWorkbook.VBProject.VBComponents(Me.CodeName)
    Set CodeMod = VBComp.CodeModule
    LineNum = CodeMod.CountOfLines + 1

    codeText = codeText & "Private Sub " & buttonName & "_Click()" & vbCrLf
    codeText = codeText & "  Dim buttonName As String" & vbCrLf
    codeText = codeText & "  buttonName = """ & buttonName & "" & vbCrLf
    codeText = codeText & "  CommonButton_Click buttonName" & vbCrLf
    codeText = codeText & "End Sub"
    CodeMod.InsertLines LineNum, codeText
End Sub

enter image description here

More Related Contents:

  1. How to add events to Controls created at runtime in Excel with VBA
  2. VBA: Using WithEvents on UserForms
  3. Wait until Application.Calculate has finished
  4. VBA – Update Other Cells via User-Defined Function
  5. JavaScript: remove event listener
  6. How to remove a lambda event handler [duplicate]
  7. Which Python packages offer a stand-alone event system? [closed]
  8. iOS – forward all touches through a view
  9. Accessing an object’s property from an event listener call in JavaScript
  10. How to pass event as argument to an inline event handler in JavaScript?
  11. jQuery equivalent of JavaScript’s addEventListener method
  12. Determine whether user clicking scrollbar or content (onclick for native scroll bar)
  13. How can I make a single event handler handle ALL Button.Click events?
  14. How to handle add to list event?
  15. How would it be possible to remove all event handlers of the ‘Click’ event of a ‘Button’?
  16. What is the proper way of doing event handling in C++?
  17. Handling “onclick” event with pure JavaScript
  18. How to unbind a specific event handler
  19. How to use the GWT EventBus
  20. Should I always disconnect event handlers in the Dispose method?
  21. Why people use message/event buses in their code? [closed]
  22. visibilitychange event is not triggered when switching program/window with ALT+TAB or clicking in taskbar
  23. How can an Excel Add-In respond to events in any worksheet?
  24. What properties can I use with event.target?
  25. Backbone.js : change not firing on model.change()
  26. Detect when a form has been closed c#
  27. ModX in event.state in tkinter?
  28. How can I remove a JavaScript event listener?
  29. Creating a custom event listener on an Android app
  30. GWT Custom Events

Leave a Comment