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

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.

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

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