Application defined or object defined error - Not Caused by Code

[JeffTO]

Hi All

Here is a strange one

I have a command button that envokes code "mnuExit" - this code does a
bunch of different things and then closes the excel file. I have more
code in the BeforeClose and Workbook_Deactivate events.

Everything runs properly if there are no other Excel Files open -
however - IF there are other excel files open once all the code has
finished running I get a dialogue box that says:

"Applicatiob-defined or object-defined error" there is no 1004 or
anything else - and there is nothing in my code that is causing this
error - I have stepped though all of the code - I have removed all of
the code in the BeforeClose and Workbook_Deactivate events and it is
still happening

I have even removed most of the code from "mnuExit" only leaving:

ThisWorkbook.Saved = True
ThisWorkbook.Close

And the other strange thing about all of this is that if you "X" out
of the file and not use the "Exit Button" then none of this happens

Anyone have any thoughts on this or seen something similar?

Thanks in advance for any comments

Jeff

 

[JeffTO]

I have done some further testing:

I created a new workbook and put the following code:

Sub MYexit()
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub


When I run the macro with other workbooks open or closed there is no
problem

However...

When I create a toolbar with a single button and assign MYExit to the
button - I get the same "Application-defined or object-defined error"
ONLY if other workbooks are open.

So it looks like the problem is being cause by running the macro with
a command button when other workbooks are open - again there is no
code causing this error - so I am not sure how to trap it so I can
avoid the error message being shown.

Any thoughts would be great

Thanks again,

Jeff

 

[Gary''s Student]

When you close ThisWorkbook, you are closing the workbook containing the
running macro. If there is more than one workbook open, Excel may be
confused about what to do next.

If there is another workbook open, try Activating it before closing
ThisWorkbook.

Let us know if this helps.

 

[JeffTO]

Hello and thanks for the reply

That did not work - before I went to the trouble of writing code to
check for open workbooks and then activating one of them I tried it
with a hard coded defined workbook ("Book9") and it did activate the
workbook but I am still getting the same error.

It has to do with running the code from the Commandbar button - when I
run the code via Tools/Macro/ and select the macro name - the code
runs fine - so I think there is something with trying to close a
workbook with a Commandbar button - at least that is what it looks
like to me.

Thanks,

Jeff

 

[JeffTO]

One more piece of testing - I added a Command Button to one of the
sheets in the file and assigned the same "MyExit" macro to the new
button.

It works perfectly fine.

So - it is ONLY a problem when trying to run the code from a
commandbar button WITH other spreadsheets open.

I am sure I am not the only one to stumble accross this

BTW - I am running Excel 2003 if that makes a difference or helps at
all

Thanks.

Jeff

 

[Poniente]

One more piece of testing  - I added a Command Button to one of the
sheets in the file and assigned the same "MyExit" macro to the new
button.

It works perfectly fine.

So - it is ONLY a problem when trying to run the code from a
commandbar button WITH other spreadsheets open.

I am sure I am not the only one to stumble accross this

BTW - I am running Excel 2003 if that makes a difference or helps at
all

Thanks.

Jeff

Hi everyone,
I have exactly the same problem as Jeff described in detail.. I'm
activating the code via a added menubar. I'm also using 2003.

Just hoping there is a genius out there that can help us out.

Kind regards,
Il Poniente