'Creating an Event handler' Thread RSS Feed

Creating an Event handler

Tue, 06 Dec 2005 13:31:32 -0700

Hello everyone, I'm not sure if this is possible in VBA but here goes.

I am trying to create an event handler in vba - MS Word - to handle all of the click events for a series of check box's that I have in a table.

The reason that I want to set up a single event handler is there is a large number of check box's so instead of having:

Private Sub CheckBox1_Click()
 ...code here
 ...code here
End Sub
Private sub Checkbox2_Click()
 ...code here
 ...code here
End sub
etc...
etc...
etc...

I could have something like

Private sub ActiveDocument.shapes_Click( obj at ActiveDocument.Shapes.OLEFormat)
     if obj.progID = "Forms.CheckBox.1" then
        ...code for event here
     end if
end Sub

has anyone ever done anything like this before? any thoughts.

C:\Dos
C:\Dos Run
Run Dos Run

Re: Creating an Event handler - bad news

Fri, 16 Dec 2005 13:36:04 -0700

: Hello everyone, I'm not sure if this is possible in VBA but here goes.
:
: I am trying to create an event handler in vba - MS Word - to handle all of the click events for a series of check box's that I have in a table.
:
: The reason that I want to set up a single event handler is there is a large number of check box's so instead of having:
:
:

: Private Sub CheckBox1_Click()
:  ...code here
:  ...code here
: End Sub
: Private sub Checkbox2_Click()
:  ...code here
:  ...code here
: End sub
: etc...
: etc...
: etc...
:

:
: I could have something like
:

: Private sub ActiveDocument.shapes_Click( obj at ActiveDocument.Shapes.OLEFormat)
:      if obj.progID = "Forms.CheckBox.1" then
:         ...code for event here
:      end if
: end Sub
:

:
: has anyone ever done anything like this before? any thoughts.
:
: C:\Dos
: C:\Dos Run
: Run Dos Run
:
:

Hi,
I've got bad news for you..
VBA (and VB6) doesn't support multievent handling.. You can not handle multiple events by one procedure and you can not use multiple handlers on one event.. This is possible in VB.NET with AddHandler and RemoveHandler..
VB6 could help you in this case, when you make your checkboxes dynamically and give them the same name and different indexes.. In this case, you can have one handling sub, BUT VBA in excel doesn't support indexes for controls, as VB6 does.. :(

You'll have to write a lot of

Private Sub CheckBox1_Click()
 ...code here
 ...code here
End Sub
Private sub Checkbox2_Click()
 ...code here
 ...code here
End sub
etc...
etc...
etc...

blocks.. I suggest you to give just one line of code to each of them..

Private sub Checkbox2_Click()
  HandleClick(2)
End sub
...
Private Sub HandleClick(Byval Index as Integer)
  If Index=2 then...

If anyone does know better solution, please, correct me..

Pavlin II[/size]

Don't take life too seriously anyway you won't escape alive from it!

Re: Creating an Event handler - bad news

Sat, 17 Dec 2005 16:41:31 -0700

: : Hello everyone, I'm not sure if this is possible in VBA but here goes.
: :
: : I am trying to create an event handler in vba - MS Word - to handle all of the click events for a series of check box's that I have in a table.
: :
: : The reason that I want to set up a single event handler is there is a large number of check box's so instead of having:
: :
: :

: : Private Sub CheckBox1_Click()
: :  ...code here
: :  ...code here
: : End Sub
: : Private sub Checkbox2_Click()
: :  ...code here
: :  ...code here
: : End sub
: : etc...
: : etc...
: : etc...
: :

: :
: : I could have something like
: :

: : Private sub ActiveDocument.shapes_Click( obj at ActiveDocument.Shapes.OLEFormat)
: :      if obj.progID = "Forms.CheckBox.1" then
: :         ...code for event here
: :      end if
: : end Sub
: :

: :
: : has anyone ever done anything like this before? any thoughts.
: :
: : C:\Dos
: : C:\Dos Run
: : Run Dos Run
: :
: :
:
: Hi,
: I've got bad news for you..
: VBA (and VB6) doesn't support multievent handling.. You can not handle multiple events by one procedure and you can not use multiple handlers on one event.. This is possible in VB.NET with AddHandler and RemoveHandler..
: VB6 could help you in this case, when you make your checkboxes dynamically and give them the same name and different indexes.. In this case, you can have one handling sub, BUT VBA in excel doesn't support indexes for controls, as VB6 does.. :(
:
: You'll have to write a lot of
:

: Private Sub CheckBox1_Click()
:  ...code here
:  ...code here
: End Sub
: Private sub Checkbox2_Click()
:  ...code here
:  ...code here
: End sub
: etc...
: etc...
: etc...
:

: blocks.. I suggest you to give just one line of code to each of them..
:

: Private sub Checkbox2_Click()
:   HandleClick(2)
: End sub
: ...
: Private Sub HandleClick(Byval Index as Integer)
:   If Index=2 then...
:

:
: If anyone does know better solution, please, correct me..
:
:
: Pavlin II[/size]
:
: Don't take life too seriously anyway you won't escape alive from it!
:
:
:

Thanks for the Reply - sorry I haven't been back here sooner I did find a simple solution to my problem.

first you add a class module to you vba project and create a simple var

dim mycheck as checkbox
private sub mycheck_click()
 'add all relevent code here
end sub

then you create a global array of you new class in your document.

then in your init event for your document
you iterate through the checkboxs in your document and redim your new array then set the ubound to equal the current checkbox.

when this is done - all of the checkbox's respond to the event coded in the class module with out the effort of defining actions for each event.

I don't have th vba code with me - but next week I will grab it and post the actual code so everyone can see.

it works great.

C:\Dos
C:\Dos Run
Run Dos Run

Re: Creating an Event handler - bad news

Sat, 06 Jun 2009 07:01:52 -0700

Just found your post from 2005. I have the exact same problem and would appreciate a copy the code you found

DougieW

Re: Creating an Event handler - bad news

Sun, 04 Mar 2012 12:14:31 -0700

I found a solution!!!

I have a user form named frmCalendario with a frame, the frame contains 42 labels, where de label1 name is lbl1, label2 name is lbl2, ... label42 name is lbl42.

class Module "clsLabel" code:

Public WithEvents myLabel As MSForms.Label

Private Sub myLabel_Click()
msgbox("myLabel Event")
End Sub

user form code:
Private WithEvents lbl As Label
Private newLabel() As clsLabel

Private Sub UserForm_Initialize()
Dim i As Integer
Dim etiq As String

For i = 1 To 42
etiq = "lbl" & i
Set lbl = frmCalendario.Controls.Item(etiq)
Set newLabel(i) = New clsLabel
Set newLabel(i).myLabel = lbl
Next i
End Sub

And ... it works!!!!!

Re: Creating an Event handler - bad news

Sun, 04 Mar 2012 12:16:22 -0700