Automatically run a macro when opening an Excel file

[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

 

[job]

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

 

[John Wilson]

Raphael,

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

Sub Auto_Open()
' your code
End Sub

John

 

[Raphael Saddy]

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

 

[John Wilson]

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

 

[Raphael Saddy]

John,

EXCELENT! EXCELENT! EXCELENT!

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

Thank you!

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

Bye.

raphael

 

[John Wilson]

Raphael,

Happy to have been a help.
Thanks for posting back.

John

 

[Raphael Saddy]

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

 

[John Wilson]

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

 

[Raphael Saddy]

Hi John!

Sorry to answer so late!

Thank you once more for your help!!!!!

Bye,


Raphael

 

[John Wilson]

Raphael,

Sorry to answer so late!

Not a problem. Glad to hear that it worked.

John