Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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
 
Old April 3rd, 2007, 10:14 AM
Registered User
 
Join Date: Apr 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Worksheet_Change executing selectively

Hello and thanks for taking the time to look at my question!

I have a Worksheet_Change method in my sheet module which has suddenly begun working selectively. I have several target ranges in my worksheet that I need to watch for changes. One range of cells is all pull-down menus (a list of electrical loads through excel's validation), another two cells are looking at the name and location of an electrical panel.

When the user selects an electrical load, the worksheet_change method updates a legend of definitions. When the user enters a new name or location, the method updates similar fields elsewhere on the same sheet.

The problem is, excel has stopped executing worksheet_change when a load is chosen or location entered. It only executes when the aforementioned load and location are deleted. However, the name field triggers worksheet_change just fine!

I have attempted to put breakpoints on the worksheet_change method; Excel does not even execute the method as described above. I've tried breaking on the toggling of Application.EnableEvents (which occurs early on in the execution of worksheet_change). I currently have a global watch on Application.EnableEvents and I'm quite sure it's TRUE before I attempt to trigger a worksheet_change event.

My next step is to create a brand new workbook and copy my spreadsheet and code into it. I'll post the results of that experiment.

Thank you for your time and responses!
Len French
 
Old April 3rd, 2007, 03:24 PM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Have you checked to make sure the cell that is bound to the dropdown is changing?

Personally I'd use the activex dropdown from the Control Toolbox and use it's change event:

Private Sub My_ComboBox_Change()
  *********Your Code************
End Sub

This way the only time you have code executing is when the dropdown content changes.

 
Old April 3rd, 2007, 03:34 PM
Registered User
 
Join Date: Apr 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

allenm,
I do use the combobox_change method for one combobox but the dropdowns I was referring to are in-cell via excel validation.

I have been able to resolve the problem and it boils down to this:
There was both corruption in the sheet as well as a limitation in Excel 97.

To solve the corruption, I was able to bring up an old revision of the sheet without the worksheet_change problems and copy over the new code. That worked except that cells using dropdown lists via excel validation still didn't trigger worksheet_change events.

After some research, I found this article that explains how said lists don't work in excel 97 and it's a known issue. I fixed this using worksheet_calculate as mentioned in the article.

Thank you for your help!
Len French

 
Old April 3rd, 2007, 03:59 PM
Registered User
 
Join Date: Apr 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Another interesting point to note:

I found that a certain cell was not triggering worksheet_changes unless conditional formatting was absent. If I added the conditional formatting back, the cell would fail to trigger worksheet_change events. I fiddled with it a bit and found that it was the manner of the condition that caused it to fail.

I was calling a vba function (which returned a boolean) inside of the conditional formatting. It appears this managed to override the call to worksheet_change.

I rewrote the conditional formatting to use an excel function instead. Works like a charm!

FYI.
Len French






Similar Threads
Thread Thread Starter Forum Replies Last Post
Please help!!!!!Private Sub Worksheet_Change VicM Excel VBA 1 March 6th, 2008 10:35 PM
EXECUTING A STRING (HELP PLEASE!!) hossrad SQL Server 2000 1 May 15th, 2007 07:37 AM
Selectively showing an icon in datagrid GuyB ASP.NET 1.0 and 1.1 Basics 3 November 25th, 2005 01:19 AM
Excel Worksheet_Change option mtowle Excel VBA 1 October 21st, 2005 09:27 AM
How to step into Worksheet_Change Piblon Excel VBA 2 December 17th, 2003 04:11 AM





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