AutoExec function in Excel???

[Scand]

Does it has in VBA for Excel the same function "AutoExec" and "AutoOpen" like these in VBA for Word?

Thanks
Scand

[JHausmann]

Worksheet, in Excel, has the following events:

Activate
BeforeDoubleClick
BeforeRightClick
Calculate
Change
Deactivate
SelectionChange


Putting code in the Activate event will "do stuff" when the worksheet is opened.

[Scand]

Thank you for your Help.

For me VBA is a new language, so i don't have a big knowledge about it.

Thanks
Scand

[KenX]

As a matter of fact, yes, there is a sub
(if present) that is automatically ran upon
opening an Excel file (except of course
if you disable macros)...

it is the sub procedure Auto_Open()

Sub Auto_Open()
   msgbox "Welcome to my Excel file"   
End Sub

[Scand]

Thank you very much.

That's exactly what i want.
When the Auto_Open function exists, then i think the Auto_Close function should also exists.

Thanks a lot
Scand

[Scand]

Now with this function, it only works with my macro, when i open my macro, the code works, But i want, that the code works with evry file open in excel.

How can i make, that the function Auto_Open is called by evry Workbook i'll open?


Thanks
Scand

[KenX]

This depends on the version of Excel you're using.

*******************
1) Excel 5
a) Unhide your "Personal Macro Workbook"
(Use Window->Unhide command)
b) Write the sub Auto_Open()
c) Hide the "Personal Macro Workbook"

*******************
2) Excel 1997 and up...there is still
"help" on "Personal Macro Workbook" but
I can't unhide it. Anyway, here's a
crude way to go around this problem:
just save the Excel file containing
the sub Auto_Open() in the directory
"..\Office\XlStart"

NOTE: everytime you open an Excel file,
this file (the one you saved) is also
opened...sigh
I have been trying to find a way other
than the step above but to no avail...
Please inform me if you have a solution
other than this...
*******************

[Edited by KenX on 03-22-2000 at 05:47 AM]

[KenX]

If you are using Excel 97 up,
make your sub procedure NOT in the
"ThisWorkbook" but rather, on a new
module (use Insert -> Module in the
VB Editor)

[Scand]

My Macro is in the "Office\XlStart" path, the function "Auto_Open" is in a new module and i'm using Office 2000.
But it wont work with the Auto_Open, the code in this function appears only when i open my macro, not when i open an other Excel file. Why this?
I'm looking for an other solution, when i've found another one, i'll post it.

Thanks
Scand

[JohnBrockstar]

I've managed to get this working - if you're using excel 97+
go to record macro, save in personal macro workbook, stop recording - edit the code and save. Every time you open excel it will perform the routine.
If you want to perform this when you open a new workbook from within the app - try the following - create another macro in your personal macro book, it should look something like this:

Workbooks.Add

then add your code after the above statement ie:

Workbooks.Add
Range("a1").Activate
ActiveCell.Value = "working"

then customise the buttons and assign this macro to the 'new' button. that should do it!