Automatically run a macro when opening an Excel file

  • Thread starter Thread starter Raphael Saddy
  • Start date Start date
R

Raphael Saddy

Hi everyone!

I would like to know how could I run a macro automatically
when a user opens an Excel file. That is, when someone
doubleclick the file on the desktop (for example), the
file opens and runs a specified macro.

Thanks in advance!

Raphael
 
you simply have to implement the function Open of your
workbook, and call your macro as following

Private Sub Workbook_Open()
Call yourMacro

End Sub
 
Raphael,

The easiest way to do it would be to rename your macro as Auto_Open

Sub Auto_Open()
' your code
End Sub

John
 
Hi!

Thank you all for your quick answer! Really great!

Would it be possible to run a macro when the user
closes the file? I mean, when he closes the file, this
macro would run a code to undo everything that the
opening macro did.

Sorry not to have asked that before!

Thanks in advance.

Raphael
 
Raphael,

Yes, you can run a macro just before the file is closed.
You'll need to use the Workbook_BeforeClose event.
From the VBA editor, double click on "ThisWorkbook"
in the "Projects" window and paste the following into
the pane on the right:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' your macro here
End Sub

BUT!!!!!!!

It's probably not the "Close" event that you want.
Remember that when you open a workbook, you're
only placing a copy of that workbook into memory.
What you started with is still unchanged on your hard drive.
You can run your macros, do whatever you want but if you
close without saving, your saved copy doesn't change.
If you are saving and want to clear your stuff out, you
might want to use the Workbook_BeforeSave Event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' your macro here
End Sub

John
 
John,

EXCELENT! EXCELENT! EXCELENT!

You have helped me very very much!!!!!

Thank you!

Hope you have a very special year in 2004....

Bye.

raphael
 
Hi John!

Once more I need your knowledge... sorry about that! :)

Remember the macro you sent me?

Sub Auto_Open()

Application.DisplayFullScreen = True

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayFullScreen = False

End Sub

I would like to do the following: when the user opens the file, it opens
in full screen. When he closes the file, the full screen mode turns off.
This two macros works just fine, except for the fact that when the user
clicks on the Cancel button when Excel asks for saving the file, the
full screen turns off and he goes back to the file, with all features
and commands.

My question is: is there a way to let the full screen activated even
when the user clicks on the cancel button in the save window?

Thanks once more!

Raphael
 
Raphael,

There are a couple of things that you could do.
One (if you don't need them to save the file) would be to
turn off the alert (place at the top of your Before_Close event).
Application.DisplayAlerts = False
or you could fool the Workbook into thinking it's already
saved (whether it has been, or not).
ThisWorkbook.Saved = True

John
 
Back
Top