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 16th, 2006, 04:49 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 Conflicting Event Handlers

I've got a number of event handlers in an Excel spreadsheet and they seem to be interfering with each other and/or Excel is getting "confused".

I have Worksheet_Change in two worksheets triggering various macros when certain cells change.
I have Workbook_Open to initialize a global variable and check to see if this is the latest version of the workbook.
I have Workbook_BeforePrint to check the global variable and if it's been set (by one of the change events), prompt the user to see if they want to print both worksheets.

When I first open my file and Excel, everything works great. Then if I close the file (not Excel) and reopen the file, things start to degrade. Sometimes the Open won't trigger. Sometimes the Print won't trigger. And Sometimes one or all of the change procedures won't work. But if I close Excel and open it all again, they work fine.

Anybody have any ideas what's going on and how I can fix it? I didn't want to post all the code here since there's so much of it, but if there are parts of it that would help troubleshoot this issue, I'm happy to post it.

Thanks much!
  #2 (permalink)  
Old February 20th, 2006, 05:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

I've never seen anything like this.

The code may be corrupted although this is not certain by any means. You could try Rob Bovey's Code Cleaner http://www.appspro.com/utilities/Cleaner.asp to try and sort it. Otherwise I'm out of ideas.

There are, of course, standard reasons why events may not fire off (written about Workbook_Open):
1) If you turn off events, then workbook_open won't fire.
Code:
application.enableevents = false
2) If you hold down the control or the shift key when you're opening a workbook, auto_open and workbook_open won't fire.
3) If you are opening a workbook via a macro and you used a shortcut key for that macro that included the shift key, then excel gets confused and will not fire the workbook_open or auto_open.

But it doesn't sound like any of these are the problem.


  #3 (permalink)  
Old February 20th, 2006, 05:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Ooops, that link dosen't work. Have a look on google for Rob Bovey's Code Cleaner and you should find it soon enough.

Maccas

  #4 (permalink)  
Old February 24th, 2006, 03:00 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. You were right...I had a line I didn't need in the Workbook.BeforePrint macro (application.enableevents = false) so once I printed, nothing else would work because they were disabled.

Thanks again!


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
Using Multiple Event Handlers in Excel chp Excel VBA 4 February 15th, 2006 06:25 PM
Dynamically change event handlers oranginalab Javascript How-To 2 July 31st, 2003 10:11 AM





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