What's the difference with Excel CLOSE types? 1

[Elysium]

I have a spreadsheet that has several combo boxes on it with code for the "change" event. Everything works fine when they are used. In addition, I have some code that fires on the workbook's "beforeclose" event, but it is not related at all to the "change" events on the combo boxes. My problem is this: when I close the workbook by clicking on the "x" for the workbook (not the highest one for the Excel application) or I choose "close" from the File menu, I don't have any problems. However, if I click on the "x" for the Excel application (the one at the very top right), I get a run-time error of two types:

01) Method Sheets of object _Global failed
---- or ----
02) Object variable or With Block variable not set

What gives? Especially since the highlighted portion of code has nothing to do with the instructions in the "BeforeClose" event of the workbook. Here's the code I am using:

#### This is what's highlighted as an error ####
#### Placed in the Volume_Report worksheet ####
Private Sub cboAE_Change()

Sheets("XML_Data&quot.Range("F2&quot = cboAE.Value

End Sub



#### This is the code run on the workbook close ####
#### Placed in the "ThisWorkbook" area ####
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Volume_Report&quot.cmdCreateXML = True

End Sub


Why would this happen?

 

[MatthewGB]

Elysium,

I have the same problem that you describe. What I think is happening is that when you close down Excel, for some unknown reason any comboboxes the worksheets have their _Change events triggered. I think the error messages are coming up because the actual worksheets have been closed by that stage, and your _Change code cannot find the appropriate ranges or worksheets. ie. Excel shuts down the worksheets, runs the _Change code on your comboboxes and then can't find the worksheets.

I have spent weeks trying to determine why this occurs. Even disabling events in the Workbook_BeforeClose event does not stop this from occuring. I think the only way around this is to put some Error handling codes in each of the _Change events to stop the messages from popping up.

Anyway, let me know how you go. If i can find any better resolution on this matter i will let you know.

HTH

Matt

 

[Elysium]

Matt,

I tried something rather obscure and coincidentally, it worked. I put "ThisWorkBook" in front of the problem combo boxes and the problem went away. Ex:

Private Sub cboAE_Change()

ThisWorkBook.Sheets("XML_Data&quot.Range("F2&quot = cboAE.Value

End Sub

Is this the fix?? I am not sure, but the errors have stopped. However, I agree with you about putting some error handling routine in there.

If I find something I will let you know too. Thank you for the reply!!

Randy

 

[MatthewGB]

Randy,

Sorry about the extremely late reply to this post, but even though I had notification marked, I never got your reply! It was only luck that I found your reply while looking at another problem!
Anyway, I did as you suggested and the problem has seemed to disappear. In the interim I have been changing the combo's back to the LostFocus event, but this solution is much better.

Thanks for the help on this, and I will keep digging around to see if I can find out the real reason why the error occurs in the first place.

Matt