Programatically add an event handler

DavidCroft

New Member
Joined
Oct 16, 2002
Messages
19
I have an application where I'm programmatically adding controls to an Excel User Form. I'd like to be able to add/associate event handlers with these control. For instance, programmatically adding a text box and an associated Before Update event handler. Any ideas?
David
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Yes, you can do this using Class modules. There is an example of this in "Excel 2000 Programmers Guide".

Unfortunately, I don't have my example file with this here right now. If no one else has answered by this evening, I will post an example with I get home.

Sorry I can't be of more help right now.
 
Upvote 0
I'd seen something similar at the j-walk, but I didn't like it too much. I hate using VBA on the VBE. I'll post my solution later though. (I think it came from the part of the book that Stephen Bullen wrote)
 
Upvote 0
Juan Pablo, Mark,
Thanks so much for your prompt response. I've looked through the code that Juan Pablo referred to. Its my understanding that in the example the code is programmatically creating a temporary user form with event procedures that is then used and deleted. I played with the stuff shown and its pretty cool.

However in my application I want a form with an "Add More" button that adds a series of controls representing an "entry" that the user can input into. Repeated clicks on Add More add more "entries". I want to associate event handlers with these entries to validate input etc.. I need to add both the control and its event handlers during the same execution. I had tried a similar approach to the InputBox example by adding the event handler to the forms code module when the control is added. However, the event procedure does not got triggered (I kind of didn’t expect it to!)

David
 
Upvote 0
Well, I think that you need to be careful with the name of the control being added, to match the event handler, that should be the problem that you're facing...
 
Upvote 0
Juan,

Is it reasonable to expect VBA to allow code to be added to modules that have already been converted to p-code (or z-code or whatever code that is)?

The userform module gets compiled before the userform gets shown. So surely it shouldn't be possible to dynamically add controls to a userform that is already in memory?
 
Upvote 0
Well, Mark, depends on how you see it. If it was fully compiled, then I guess that yes, that should be the way to go, but, I have noticed that VBA makes some re-compilations, even at run time !

The following code worked ok. It adds buttons, and uses a class module (As I learned from Bullen) to get the event to fire.

The commented code almost worked, it added the button, and added the event code to the userform (The 5 is because I had only 3 sheets, the ThisWorkbook and the Userform), but, the button didn't respond to the event, altough the name was it ! and, I was getting an error when I tried to add another control.

This goes in the Userform's module. At design, it should only has one button (CommandButton1), and put it somewhere on the right, so it won't "interfere" with the added ones.
<pre>Dim Tp As Long
Dim Ctr As Long
Dim MyCol As Collection

Private Sub CommandButton1_Click()
'Dim UF As Object
'Dim i As Long
'Dim StrCode As String
Dim Btn As Object 'MSForms.CommandButton
Dim Mycls As New clsBtn

'Set UF = ThisWorkbook.VBProject.VBComponents(5)
Ctr = Ctr + 1
Set Btn = Me.Controls.Add("Forms.CommandButton.1", "MyBtn" & Ctr)
With Btn
.Caption = .Name
.Left = 10
.Top = Tp
.Width = 60
.Height = 18
End With
Tp = Tp + 22
Set Mycls.Btn = Btn
Mycls.Nm = Btn.Name
MyCol.Add Mycls
' StrCode = "Private Sub " & Btn.Name & "_Click()" & vbCrLf
' StrCode = StrCode & " MsgBox " & Chr(34) & "Clicked on " & Btn.Name & Chr(34) & vbCrLf
' StrCode = StrCode & "End Sub"
' With UF.CodeModule
' i = .CountOfLines
' 'MyText is a variant which will hold the answer the user pressed
' .InsertLines i + 1, StrCode
' End With
End Sub

Private Sub UserForm_Initialize()
Tp = 20
Ctr = 0
Set MyCol = New Collection
End Sub</pre>
and the following is the class module, named clsBtn
<pre>Public WithEvents Btn As MSForms.CommandButton
Public Nm As String

Private Sub Btn_Click()
MsgBox "Clicked on button: " & Nm
End Sub</pre>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top