How to add events to Controls created at runtime in Excel with VBA

by

The code for adding a button at runtime and then to add events is truly as simple as it is difficult to find out. I can say that because I have spent more time on this perplexity and got irritated more than in anything else I ever programmed.

Create a Userform and put in the following code:

Option Explicit


Dim ButArray() As New Class2

Private Sub UserForm_Initialize()
    Dim ctlbut As MSForms.CommandButton
    
    Dim butTop As Long, i As Long

    '~~> Decide on the .Top for the 1st TextBox
    butTop = 30

    For i = 1 To 10
        Set ctlbut = Me.Controls.Add("Forms.CommandButton.1", "butTest" & i)

        '~~> Define the TextBox .Top and the .Left property here
        ctlbut.Top = butTop: ctlbut.Left = 50
        ctlbut.Caption = Cells(i, 7).Value
        '~~> Increment the .Top for the next TextBox
        butTop = butTop + 20

        ReDim Preserve ButArray(1 To i)
        Set ButArray(i).butEvents = ctlbut
    Next
End Sub

Now you need to add a Class Module to your code for the project. Please remember it’s class module, not Standard Module.

The Object butEvents is the button that was clicked.

Put in the following simple code (in my case the class name is Class2).

Public WithEvents butEvents As MSForms.CommandButton

Private Sub butEvents_click()

    MsgBox "Hi Shrey from " & butEvents.Caption

End Sub

That’s it. Now run it!

More Related Contents:

  1. Wait until Application.Calculate has finished
  2. VBA – Update Other Cells via User-Defined Function
  3. Handling Events for OLEObject CommandButtons created at Runtime
  4. Calling a function when ng-repeat has finished
  5. VBA: Using WithEvents on UserForms
  6. iOS – forward all touches through a view
  7. Determine whether user clicking scrollbar or content (onclick for native scroll bar)
  8. How to use Events with Option Button Controls on Userform [duplicate]
  9. Differences between Excel’s Form Controls & ActiveX Controls
  10. Exposing .NET events to COM?
  11. How to catch printer event in python
  12. Catch Textbox Scroll Event?
  13. How can an Excel Add-In respond to events in any worksheet?
  14. What properties can I use with event.target?
  15. Backbone.js : change not firing on model.change()
  16. How to capture process output asynchronously in powershell?
  17. How to add a menu item to the default right click context menu
  18. GWT Custom Events
  19. Somehow it’s not copying [closed]
  20. How do you run your own code alongside Tkinter’s event loop?
  21. Are there disadvantages in putting code into Userforms instead of modules?
  22. How to search for string in an array
  23. What is meaning of boolean value returned from an event-handling method in Android
  24. Assign keyboard shortcut to run procedure
  25. How can VBA connect to MySQL database in Excel?
  26. return csv file as recordset
  27. Getting Excel to refresh data on sheet from within VBA
  28. Why am I having issues assigning a Range to an Array of Variants
  29. How to have vba execute every 10 minutes?
  30. How to highlight selected text within excel

Leave a Comment