Code for button runs and causes "1004" error on Exit and Save

I'm not a programmer, although I've written macros for several different programs starting with Lotus 123 2.01a. Excel VBA is still challenging to get my head around. Anyway,

My current project is a Excel 2003 / 2007 (in 2003 compatiblity mode) worksheet for in-house use. I've dilebratedly tried to keep code to a minimum and accomplished desired effects with conditional formatting, etc. I have, though used some code.

The "main sheet" has a Combo Box control (cmbTeam) with the following basic code "on change"

Private Sub cmbTeam_Change()
    Worksheets("ConstantData").Range("celSelectedTeam").Value = cmbTeam.Value
    If cmbTeam.Value = "" Then
        cmbTeam.BackColor = vbYellow
    Else
        cmbTeam.BackColor = vbWhite
    End If
End Sub

This works fine, most of the time. It always works when actually using the workbook. The only problem is when EXITING Excel. If I Exit (Alt-F4, etc) with the workbook open and choose "Yes" to "do you want to save changes", I get a Runtime Error 1004, "Method 'Worksheets" of object '_global' failed. If I save and close the workbook, then exit, I do NOT get this error.

I have found a couple of ways of preventing the error, or preventing it from interferring, the easiest being

On Error Resume Next

at the top of the Sub. I can also force the Sub to Activate the worksheet being modified (ConstantData) and then Activate the main worksheet so the code looks like this

Private Sub cmbTeam_Change()
Application.ScreenUpdating = False
Worksheets("ConstantData").Activate
Worksheets("ConstantData").Range("celSelectedTeam").Value = cmbTeam.Value
Worksheets("Assumptions").Activate
Application.ScreenUpdating = True
    If cmbTeam.Value = "" Then
       cmbTeam.BackColor = vbYellow
    Else
       cmbTeam.BackColor = vbWhite
    End If
End Sub

My question, though, is why the bleep is the Sub even running and causing an error when I Exit and Save? It has already run and NOT caused any error when the actual event that is supposed to trigger the code occured. And it only happens when I Exit and answer "Yes" to save. Not when I save and close the workbook, nor when I close the workbook w/o saving? I think it might have to do with Excel 2007 forced-recacluating of 2003-format worksheets?

Before you ask, here is the code that runs OnClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Assumptions").Select
    Application.Goto Reference:="celHome"
    RstHome
    ' Call sub to Restore CTRL+HOME to default
    Application.EnableEvents = True
End Sub
 
Sub RstHome()
'Restore CTRL+HOME to default action
    Application.OnKey "^{HOME}"
End Sub

Since I've been able to make Excel ignore the error with OnErrorResume, this isn't critical. But it's bugging the bleep outta me. I'd like to know why it is happening so I can avoid it in the future, if possible.

Thanks!
- Sequoia

also posted at:http://www.ozgrid.com/forum/showthread.php?t=145662 (and resolved there)... it's a big web out there ...

Please take a few minutes to read our forum rules before posting again.

Thanks.

It's a big web out there. If you don't want me to play in your sandbox, please let me know and I'll find a more friendly environment. I was just trying to resolve a riddle, I don't need any more headaches.

Treepapa,

Seems you are asking for help not us. Don't bother posting if you don't like our rules, your loss not ours