Closing FIle with Command Bar Button Problem

  • Thread starter Thread starter JeffTO
  • Start date Start date
J

JeffTO

Hi All

I posted this last week with a different title and didnt get any
responses - so perhaps my messages was not understood - so I am trying
again:

I am getting a very strange error which I have isolated but I do not
know how to get rid of:

I have a workbook with the following code:

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


When I run the macro directly from 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 an "Application-defined or object-defined error" ONLY
if other workbooks are open - I do not get this message if there are
no other workbooks 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 - I step through all the code no problem and
this error comes up after all the code runs properly - so I am not
sure how to trap it so I can avoid the error message being shown.


Any thoughts would be great

I really appreciate any ideas on this one

Thanks,

Jeff
 
Change the OnAction from MYexit to OnTimeExit

Sub OnTimeExit()
Application.OnTime Now, "MYexit"
End Sub

In passing, you could do simply
ThisWorkbook.Close False

Trust your close event code destroys the toolbar (and creates it again in
the open event)

Regards,
Peter T
 
Hi Peter

Works perfectly - thank you soooo much

And yes I do create the toolbar on open and delete on close

The actual live code is more complex than the sample I gave but the
problem was the same - so now that I have it working in the sample - I
am going to implement in my live code

Can you tell me why yours works and mine didnt? I am curious to know
what the difference is.

Thanks again - exteremely helpful

Jeff
 
Hmm .... good question ... don't know!

I'm almost sure for a while all was working fine if I added the parameter
Temporary:=True to commandbars.add(). But now that doesn't help.

At the moment I can't say why it fails with no other visible workbooks, but
the reason the OnTime fixes is because the close code is not being called
from the button.

(btw the issue is if there are/not other *visible* workbooks)

Regards,
Peter T
 
Back
Top