Add-in Error "Application-defined or Object-Defined Error"

[jeremy.haynie]

Here's the basic premise:

I created a workbook with a userform and openworkbook macros to load
the userform. I then wrote an add-in that simply opened up my workbook
from the tools menu. I forced a saveas so that users couldn't access
the original. If they canceled the saveas or userform then the
userform and my workbook (thisworkbook) would close.

If I open my workbook independently then everything works ok. When I
run the add-in and cancel the saveas or the userform I get a
"Application-Defined or Object-Defined Error". I've tried using "on
error resume next", but the error message still pops up. Although, the
workbook and userform do close, I still don't want the error message
to appear.

Any help would be appreciated.

Here is the code I'm using to close the userform and workbooks. Both
ways to close give the same error.

Is it the use of ThisWorkbook?

Thanks


Private Sub Cancel_Click()
CloseWorkbook = True
On Error Resume Next
ThisWorkbook.Close False
On Error Resume Next
Unload AutoComplete
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
If CloseWorkbook = False Then Exit Sub
'skips if OK button is pressed on Userform

Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Close False
Application.DisplayAlerts = True
CloseWorkbook = True
End Sub

 

[Peter T]

I don't follow quite what you are doing, however I wouldn't close the
workbook containing the form whilst it's running, and/or Unload AutoComplete
(whatever that is) after closing the wb with the running code.

Perhaps you could close 'ThisWorkbook' from the form with an OnTime macro,
eg

Sub CloseMe(nSave As Long)
ThisWorkbook.Close CBool(nSave)
End Sub

Sub test()
Dim sMacro As String
sMacro = " 'CloseMe " & CLng(True) & " ' "
Application.OnTime Now, sMacro
End Sub

Not sure you need to change DisplayAlerts.

Regards,
Peter T