How do I phrase the VBA to run a macro on closing the spreadsheet?

Any ideas?

Thanks
Donnie

hi,
put your code in the before close event of the workbook.
no special coding needed.

Regards
FSt1

"Donnie" wrote:

How do I phrase the VBA to run a macro on closing the spreadsheet?

Any ideas?

Thanks
Donnie

Where do I find the close event?
I'm not sure where to find the VBA, only the Macros.
Thanks anyway

"FSt1" wrote:

hi,
put your code in the before close event of the workbook.
no special coding needed.

Regards
FSt1

"Donnie" wrote:

How do I phrase the VBA to run a macro on closing the spreadsheet?

Any ideas?

Thanks
Donnie

hi,
Open the vb editor. Alt+F11
in the project window (left), expand your project (file name).
Double click ThisWorkbook
in the object box (top middle) cick the down arrow then click workbook.
default is workbook_open(). ignore for now.
in the procedure box (right of object box) scroll to and select Beforeclose.
you wil get this below. you can delete the default WorkbookOpen.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Put your code between Private sub and end sub.
close the vb editor and save file

Regards
FSt1


"Donnie" wrote:

Where do I find the close event?
I'm not sure where to find the VBA, only the Macros.
Thanks anyway

"FSt1" wrote:

hi,
put your code in the before close event of the workbook.
no special coding needed.

Regards
FSt1

"Donnie" wrote:

How do I phrase the VBA to run a macro on closing the spreadsheet?

Any ideas?

Thanks
Donnie

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'your code
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Donnie" wrote in message
...
How do I phrase the VBA to run a macro on closing the spreadsheet?

Any ideas?

Thanks
Donnie

You could also use a procedure that's called:

sub Auto_close()

Placed into a general module.

But you may want to rethink your approach. If you put code in one of those
workbook_beforeclose or auto_close procedures, any changes that your macro makes
won't be changed if the user says no to save changes--and to save those changes
automatically could be disasterous.

If I open a workbook and delete 15 or 20 of the important sheets and I want to
close without saving, your code runs and an automatic save could kill the next
person who uses the workbook.

An alternative is to make all your adjustments in the Workbook_open event (under
ThisWorkbook--same location as the _beforeclose event) or call the subroutine
Auto_open (in any general module).

That way, the workbook is saved anyway the user wants, but the next time it's
opened, the auto_open routine runs and sets up the workbook the way you want it.

(This assumes that macros are enabled--but that's the same assumption for the
_beforeclose and auto_close routines, too.)



Donnie wrote:

How do I phrase the VBA to run a macro on closing the spreadsheet?

Any ideas?

Thanks
Donnie

--

Dave Peterson

Sub Auto_Close()
'your code here
End Sub

that is how i do it :-)
(the auto_close makes it run at close)
"Donnie" wrote:

How do I phrase the VBA to run a macro on closing the spreadsheet?

Any ideas?

Thanks
Donnie