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"
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.Please Login or Register to view this content.
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 thisPlease Login or Register to view this content.
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?Please Login or Register to view this content.
Before you ask, here is the code that runs OnClose:
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.Please Login or Register to view this content.
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