Application-defined or object-defined error on closing

[Peter Rooney]

Good morning everyone.

I have a workbook that customises the Excel Menubar. One of the commands
allows the user to open another workbook, and to close the active workbook,
the user selects a command from the customised menu that runs the macro
"CloseMe" as shown below.

ScreenReset is used to reset a number of screen customizations. The MsgBox
shows me if "ScreenReset" has run OK (which it does), but when the workbook
closes, the message "Application-defined or object-defined error on closing"
is displayed.

When I click OK on this message and the workbook (which is called from
another workbook) closes, the Workbook_Activate macro in the calling workbook
(which then becomes the active workbook) doesn't kick in.

Sub CloseMe()
On Error Resume Next
'Application.EnableEvents = False
'Application.DisplayAlerts = False
Application.ScreenUpdating = False
RequestPassword = False
ScreenReset
MsgBox ("Past ScreenReset")
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub


Can anyone hazard a guess as to what's going on?

Thanks in advance

Pete

[Peter Rooney]

OK - the deafening silence tells me it was this week's stupid question - how
does the macro finish running if it's last line is "activeworkbook.close",
thus closing the workbook that contains the macro..? Doh!

I re-enabled the "Close" command from the "File" menu instead.

I wear my "Dunce of the Week" hat in shame... :-)

Pete



"Peter Rooney" wrote:

> Good morning everyone.
>
> I have a workbook that customises the Excel Menubar. One of the commands
> allows the user to open another workbook, and to close the active workbook,
> the user selects a command from the customised menu that runs the macro
> "CloseMe" as shown below.
>
> ScreenReset is used to reset a number of screen customizations. The MsgBox
> shows me if "ScreenReset" has run OK (which it does), but when the workbook
> closes, the message "Application-defined or object-defined error on closing"
> is displayed.
>
> When I click OK on this message and the workbook (which is called from
> another workbook) closes, the Workbook_Activate macro in the calling workbook
> (which then becomes the active workbook) doesn't kick in.
>
> Sub CloseMe()
> On Error Resume Next
> 'Application.EnableEvents = False
> 'Application.DisplayAlerts = False
> Application.ScreenUpdating = False
> RequestPassword = False
> ScreenReset
> MsgBox ("Past ScreenReset")
> Application.ScreenUpdating = True
> ActiveWorkbook.Close
> End Sub
>
>
> Can anyone hazard a guess as to what's going on?
>
> Thanks in advance
>
> Pete
>
>