Triggering a specific macro upon opening spreadsheet

[cleslie]

I need some help with triggering a specific macro upon opening a spreadsheet i.e. the macro sends out an email if the data is outdated in a specific range on the sheet.

I have the "basic" email portion set up in the macro, but have FRIED my brain looking thru this data on the web site looking for guidance on how to trigger the macro as soon as the sheet is opened.

I know enough to know that I could be dangerous to myself with out help.

 

[Tazguy37]

Welcome to MrExcel Board!

Have you tried putting the code in a Workbook_Open() event, rather than a standard module (Module1, etc.)?

Got code to post?

 

[cleslie]

Now I will show my complete ignorance on this by saying huh? I am extremly new to this portion of the functionality of excel and thus came to MrExcel for help, so if you could put that into lay terms it would be most helpful.

"Coworker" says could you put a southern drawl on that for us? lol

 

[Joe Was]

In the VBA Editor "Alt+F11" select the code module "ThisWorkbook" listed in the Project-Manager "Ctrl+R", with a double-click. At the top of the screen, at about middle width, you will see a drop-down box with the word "General" in it. Select that dropdown and pick "Workbook" the default Event for the Workbook is the "Open Event" [other Events can be selected using the next dropdown to the right!].


You can call your Sub in this event by just listing it's name, like: Sub myList()

would be run on the Open Event as:

Private Sub Workbook_Open()

myList
End Sub


You can also call it with the call syntax:

Private Sub Workbook_Open()

Call myList
End Sub


Or you can add the full code directly to this Event, like:

Private Sub Workbook_Open()
'ThisWorkbook code!

MsgBox "Welcome to my Application!"

Sheets("Sheet1").Range("A3").Select
End Sub


If the Sub is in another Workbook you can conscript the call or add Workbook Open Object code as needed here as well.

To DeBug your event copy your code to another sub and run the code or the debugger ["F8" to step through each line of code] or Save the Workbook and close it, then re-open and the Event will fire the code up.

 

[Tazguy37]

There are code modules that you run (through a menu or function key) -- these are commonly called standard or standalone modules. They'll start with a line like "Sub Macro1", for example.
Then there are code modules that run because some other event takes place (a workbook is opened or saved, you change worksheets, or change a cell's value). These are called event code modules. They will be named automatically for the event they pertain to, for example:

Sub Workbook_Open
Sub Workbook_BeforeClose
Sub Workbook_BeforeSave

You could simply call a standard module that you've created (from one of these event modules, Workbook_Open, for example) by just putting the name of the module between the "Sub" and "End Sub" lines. If we wanted to run "Macro1" when the workbook is opened, we could do something like this:

Private Sub Workbook_Open()
    macro1
End Sub

Where:

Sub macro1()
    MsgBox "test"
End Sub

• How to enter code:
  Press Alt-F11 to open the VBE.
  Press Control-R to open the Project Explorer. (May be open already)
  Click "Microsoft Excel Objects" for the file you're working on (should expand the list of the ThisWorkbook module and any sheet modules.)
  
  Select Insert, Module from the drop down menus for standard modules.
  For ThisWorkbook and sheet modules:
  Double-click the ThisWorkbook module or the sheet this code applies to.
  Select Workbook, Before Close, and Open from the dropdowns.
  
  Put your code in the right-hand window.
  Press Alt-Q to close the VBE and return to Excel.

Hope that helps!

 

[cleslie]

Is the wording on this specific to a certain version of excel? I am using excel2003.

P.S. There are now three of us sitting here trying to figure these "mystical" codes out.

 

[Tazguy37]

Is the wording on this specific to a certain version of excel? I am using excel2003.

What wording are you referring to, exactly? Joe's post gives very good instructions on how to select the different events you require.

 

[Joe Was]

The Macro code for Excel 97 may be different somewhat, but for all the other versions newer than Excel 97 the code is the same.

That being said each newer version has additional code to manage newer options that the newer versions offer the user. Most of these newer options are for little things such as Coloring Sheet Tabs, something older versions cannot do. For the standard things the code is the same.

If you try to run something that is not supported, check the help or the object browzer to see if it is listed. Sometimes you can set a reference to fix the problem. I would not worry about this yet. As it seems that you have a long way to go, before you need to deal with advanced code issues.


P.S: If you can write e-mail code, which is generally a very complex code problem, why are you asking such primative questions?

 

[cleslie]

Acutally, after getting everyone out of the office for a few minutes, using the data both of you provided I now have the macro up and running smotthly upon opening the spreadsheet.

For what it is worth, both of you have my heart felt thanks for your time and patience with me.

"Coworker" also sends her thanks for preventing my "baldness"