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
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 ...
Bookmarks