Problem with Change Event occuring with ThisWorkbook.Close

[RustyAfro]

Excel 2000
This example is a direct copy of how I can duplicate the problem. I can't figure out why the Change event would fire right after ThisWorkbook.Close. It causes an error when the workbook closes because the Change event has some activesheet references in it that aren't applicable if Workbook is closed and not on that sheet.

1. Open the excel workbook.
2. Some code dynamically enables the "cbMentalHealth" combobox in the "wksExhibitTemplate" worksheet, which is sort of a change, but not one that should cause this problem.
3. Close the excel workbook.

4. This auto_close event fires.

Sub auto_close()
       ThisWorkbook.Close False
End Sub

5. If I "Step Into" ThisWorkbook.Close False, the "Change" event of "cbMentalHealth" then fires in a totally different sheet "wksExhibitTemplate".

Private Sub cbMentalHealth_Change()
 ...some code errors because it references active sheet inappropriately
End Sub

 

[SkipVought]

Why not try turning off events...

Sub auto_close()
  application.enableevents = false
       ThisWorkbook.Close False
  application.quit
End Sub

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[RustyAfro]

Sorry this took me awhile to respond to. That's a clever idea I hadn't thought of so here's a star from me. It does work for that error.

However, I later found that the Change event was firing from other strange places as well (like clicking a button from win form that has no bearing on that sheet. I couldn't identify the cause so I just change the code in the Change event to check if the active sheet was the sheet the control with the Change event was on. If it wasn't then the code doesn't occur. If it is the code can fire safely since it's on the right sheet.

I have noticed Excel 2k is a bit buggy when dealing with large / complex applications built in it, probably because it's not really designed for such uses. It's getting time to move it to a .Net app anyways