Using Multiple Event Handlers in Excel

chp · February 13th, 2006, 05:12 PM

I have created a spreadsheet with a number of procedures that monitor parts of the spreadsheet for various activities and launch macros as needed. The trigger for some of these are the same--a change to the worksheet, although they refer to different parts of the worksheets.

Each procedures works fine on its own, but when I have multiple ones active, nothing happens for any of them. On one worksheet I have two Worksheet_Change procedures and on another worksheet I have three Worksheet_Change procedures.

Do these need to be combined into a single procedure watching the different parts of the worksheet? I've tried a bit of that, but haven't had any luck with that either--I'm not really a programmer but am quickly learning Excel VB by being thrown into the fire! My code for one of the worksheets is below.

Any help you can provide would be greatly appreciated. Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
'Capture trigger (any change to cells that contain Expense Type drop-down list)

'Call macro to respond to changes to only those cells in the specified Range.
'Target is the current active cell (the one being changed).
    Set isect_ExpType = Intersect(Range("A4:A60"), Target)
    If isect_ExpType Is Nothing Then
        Exit Sub
    Else
        Call ControlFlow
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Capture trigger (any change to cells that contain Account Number)

'Call macro to respond to changes to only those cells in the specified Range.
'Target is the current active cell (the one being changed).
    Set isect_Amt = Intersect(Range("I4:I60"), Target)
    If isect_Amt Is Nothing Then
        Exit Sub
    Else
        Call CreateSubtotal
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'If any change is made to the second page of the report,
'prompt user when they print. This sets the flag to know if anything
'on page 2 has changed.

    Application.EnableEvents = False
    MsgBox ("false = " & P2Changed) 'used for testing value of P2Changed
    P2Changed = True
    MsgBox ("true = " & P2Changed) 'used for testing value of P2Changed
    Application.EnableEvents = True

End Sub

maccas · February 14th, 2006, 04:09 AM

You'll need to combine the multiple event handlers for each sheet into one event handler for each sheet. The simple reason for this is that VBA does not know which one to call when the event is raised if there are multiple event handlers.

You say you've tried combining the procedure but I'm not sure why the following won't work:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect_ExpType As Range
Dim isect_Amt As Range
Dim P2Changed As Boolean

    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' Capture trigger (any change to cells that contain Expense Type drop-down list)
    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    ' Call macro to respond to changes to only those cells in the specified Range.
    ' Target is the current active cell (the one being changed).
    Set isect_ExpType = Intersect(Me.Range("A4:A60"), Target)
    If isect_ExpType Is Nothing Then
        Exit Sub
    Else
        Call ControlFlow
    End If

    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' Capture trigger (any change to cells that contain Account Number)
    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    ' Call macro to respond to changes to only those cells in the specified Range.
    ' Target is the current active cell (the one being changed).
    Set isect_Amt = Intersect(Me.Range("I4:I60"), Target)
    If isect_Amt Is Nothing Then
        Exit Sub
    Else
        Call CreateSubtotal
    End If

    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' If any change is made to the second page of the report, 'prompt user when they
    ' print. This sets the flag to know if anything 'on page 2 has changed.
    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Application.EnableEvents = False
    MsgBox ("false = " & P2Changed)  'used for testing value of P2Changed
    P2Changed = True
    MsgBox ("true = " & P2Changed)   'used for testing value of P2Changed
    Application.EnableEvents = True

End Sub

chp · February 15th, 2006, 03:44 PM

Thanks so much for the additional information. I've tried the code you suggested and it's catching the changes to colum A (the first test in the handler), but not column I. It's also not picking up the P2Changed flag.

Do you know of any way to debug the code in the event handler? I can't just step through the code like I can when it's in the modules since it waits for the trigger. Maybe there's a special command to force it??

Thanks again for your help.

maccas · February 15th, 2006, 03:51 PM

That's becuase I replied too quickly - without thinking! The code I should have suggested is as follows:

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect_ExpType As Range
Dim isect_Amt As Range
Dim P2Changed As Boolean

    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' Capture trigger (any change to cells that contain Expense Type drop-down list)
    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    ' Call macro to respond to changes to only those cells in the specified Range.
    ' Target is the current active cell (the one being changed).
    Set isect_ExpType = Intersect(Me.Range("A4:A60"), Target)
    If not isect_ExpType Is Nothing Then
        Call ControlFlow
    End If

    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' Capture trigger (any change to cells that contain Account Number)
    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    ' Call macro to respond to changes to only those cells in the specified Range.
    ' Target is the current active cell (the one being changed).
    Set isect_Amt = Intersect(Me.Range("I4:I60"), Target)
    If not isect_Amt Is Nothing Then
        Call CreateSubtotal
    End If

    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ' If any change is made to the second page of the report, 'prompt user when they
    ' print. This sets the flag to know if anything 'on page 2 has changed.
    ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Application.EnableEvents = False
    MsgBox ("false = " & P2Changed)  'used for testing value of P2Changed
    P2Changed = True
    MsgBox ("true = " & P2Changed)   'used for testing value of P2Changed
    Application.EnableEvents = True

End Sub

The other point is that you can debug code in an event handler. Put a break piont in at the the first available line of the handler's code and then go to the spreadsheet and perform an action that will trigger the handler (in this case change a cell on the given sheet). The code will then break out at the break point for you allowing you to debug.

Maccas

chp · February 15th, 2006, 06:25 PM

I cannot thank you enough. That seemed to do the trick. I'll tackle a lot more testing tomorrow...but it all seems to be in order now. And thank you for the tip on how to debug the event handlers.

This problem really had me stumped and you rescued me! Thank you, thank you, thank you!

Have a great night!!!