Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 13th, 2006, 05:12 PM
chp chp is offline
Registered User
 
Join Date: Feb 2006
Location: Richmond, VA, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Multiple Event Handlers in Excel

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
  #2 (permalink)  
Old February 14th, 2006, 04:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
  #3 (permalink)  
Old February 15th, 2006, 03:44 PM
chp chp is offline
Registered User
 
Join Date: Feb 2006
Location: Richmond, VA, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
  #4 (permalink)  
Old February 15th, 2006, 03:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

  #5 (permalink)  
Old February 15th, 2006, 06:25 PM
chp chp is offline
Registered User
 
Join Date: Feb 2006
Location: Richmond, VA, USA.
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!!!


Similar Threads
Thread Thread Starter Forum Replies Last Post
Event handlers jwebb Visual Basic 2005 Basics 1 June 25th, 2007 08:44 PM
ie7 keycode, event handlers erobb Javascript 0 November 24th, 2006 01:37 PM
Event handlers does not work after some changes geetageetageeta ASP.NET 2.0 Basics 0 March 6th, 2006 04:19 AM
Conflicting Event Handlers chp Excel VBA 3 February 24th, 2006 03:00 PM
Multiple event handlers for a single button monuindia2002 ASP.NET 1.0 and 1.1 Professional 1 October 16th, 2005 11:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.