How to bind VBA code to a click?

Question

I've made a graphical navigation system so the user can easily find the record he/she wants to. I'm using this on multiple forms now, and since I'm going to use it on another form, I want to standarize the code into a class instead of copy-pasting the VBA code. This way I can improve the code in one place, instead of doing the same change on all the forms.

This is how it works right now:

Dim v As New clsNav
Set v.button1 = Me.button1
Set v.button2 = Me.button2
v.init

And in v.init, I want to set up all the events like click. So when the user clicks button1, it should run a specified method.

How can I do that?

score 1 · Accepted Answer · edited May 23 '17 at 10:25

The events for the button clicks will look like this

Private Sub Button1_Click()
    ' Your code
End Sub

And they are located within the code found behind the respective forms. To be able to reuse code you simply write a sub in a separate module and then call it in the event.

Private Sub Button1_Click()
    call MySub()
End Sub

' This is in a module
Private Sub MySub()
    ' Your code
End Sub

Now this works as long as the code you have to run doesn't use controls specific to that form. If you do need to write code like that, its simply a matter of passing a control to the sub, instead of calling it by its Name

Example. Lets say when we click our buttons it updates a TextBox with today's date. The textboxes have different names on each form. txtDate1 on Form1 and txtDate2 on Form2. So how that will look is

'Form 1 Button
Private Sub Button1_Click()
    call MySub(txtDate1)
End Sub

'Form 2 Button
Private Sub Button2_Click()
    call MySub(txtDate2)
End Sub

' This is in a module
Private Sub MySub(t as TextBox) 
    t.Text = Date()
End Sub

If you're trying to do this during run time

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

seems like a good place to start. I can't imagine a situation where this would be worth the effort.

Okay. Thanks for your answer! I don't have much experience with VBA. I use it because my job is to make an Access database. It's not my choice. So if YOU don't think it's worth the effort, I'll take your word for it. This is the way I'm already doing it, since I had to continue instead of waiting for your reply :) — Friend of Kim, Jun 21 '13 at 07:59
What I do now is that I have a class module so I instantiate an object on form_load and `Set obj.cControlName = Me.ControlName` and then I set up the events like you did in this answer. Thank you again for your reply! — Friend of Kim, Jun 21 '13 at 08:01

score 0 · Answer 2 · answered Jun 21 '13 at 19:00

I have a generic class I use called frmCtrl, which has WithEvents pointers set up for different control types, with the corresponding events (click, dbl-click, etc...). I then have a function on that class to "set up" the object, so I pass the control in, and the function assigns it to the object pointer of the correct type. This routine also has a parameter for what function to call on which object, so it knows how to respond to the event.

So when I build a form that uses dynamically-added controls, I just create one of these objects for each of the controls, and set it up to call code within the form. The code in the form takes an argument of just the frmCtrl object, so it can easily see which control fired the code, and go from there. I work in Visio, so I'm able to also call a function by module and function name, so the class can handle that too. You'd have to work in how to make such a call using Access, though.

This approach can be used when your controls have been added via the VBIDE, but it may not be worth the effort to set this class up for that, when you can just use the method in the other answer here.

How to bind VBA code to a click?

2 Answers2