MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Quiting Excel97 from a worksheet macro

Posted by Jeff on August 09, 2001 3:30 PM

I need help with the code for a "save, close and quit" macro, that runs from a button within a worksheet.
I can get the save and close to work, but this still leaves the Excel app still running. Is there any tricks to get the app to close as well?
Any help greatly appreciated.

Posted by Jim on August 09, 2001 4:02 PM

In the This Workbook module write this line of code:
Private Sub Workbook_BeforeClose()
This Workbook.Saved=True

Posted by Jeff on August 09, 2001 6:24 PM

Jim: Cant get that code to work

Tried the 2 lines of code Jim, but the macro wouldnt run at all. Kept coming up with the error "Expect Sub"
Initially, I recorded the save macro, then added Active.Workbook.Close so the sheet would close.
Guess Im doing something stupid.


Posted by steve w on August 09, 2001 7:21 PM

Re: Jim: Cant get that code to (work 2 things)

Doing two things wrong

You need to put the code in the workbook module
you need to add a third line of code
end sub

Posted by Jeff on August 09, 2001 9:21 PM

Re: Jim: Cant get that code to (work 2 things)

Not having much luck. The extra code I insterted did have "End Sub" at the end, but the error was pointing at the beginining of the actual code.
The code below is what I had, which saved and closed the file OK - before I added the suggested code.
Im not sure what is meant by adding to the workbook module? When the xl file is open, I select tools/macro, then the "save" macro ( I recorded) to edit? The same macro file name seems to appear whether I select "macros in" all open workbooks, this workbook, or the actual filename.
Are we talking about two different macros here? I only can see the one associated with the open xl file. Sorry for being so thick, but the answer alludes me.
Thanks Guys,

Sub save()
' save Macro
' Macro recorded 10/08/01 by authorised user

End Sub

Posted by steve w on August 09, 2001 10:24 PM

This should help (ALT+F11 select this workbook)

Heres what you do, right click on the X to the left of file and select view code. This will take you to the workbook module, you can also right click on the page tab to do worksheet modules. What your putting code in is a general module.

You can also push ALT+F11 to get to the VBa editor and see a list of modules to the left its the one labeled "thisworkbook"

Hope this helps, its very important to put code in the proper module.

steve w

Posted by Ivan F Moala on August 09, 2001 10:29 PM

Hi Jeff
In your code for the button
Put in;


before the End sub


Posted by Jeff on August 12, 2001 1:48 PM

Re: This should help (ALT+F11 select this workbook)

Making some progress here.
Found the thisworkbook module (thanks), and inserted the code.
Came up with a Compile error when I ran the macro.
"Event procedure declaration does not match description of event having the same name"

Tried adding and removing Application.Quit to the save macro, but this made no difference.
The code I have for the thisworkbook module is:
Private Sub Workbook_Open()
Private Sub Workbook_BeforeClose()
This Workbook.Saved = True
End Sub