Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
autoexecute macros on open or new
How to execute macros on File / New or File / Open in
Excel XP ? So far I have found that if I will put the procedure named Workbook_Open in ThisWorkbook it works. But I would like to have two different actions executed on Open and on New. Should I name them Workbook_Open and Workbook_New and have them stored in ThisWorkbook ? Or is there any way to have them as a modules with some specific names ? Regards, Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
autoexecute macros on open or new
Tony,
There is no Workbook_New event, so you have a couple of choices. Either, using application events to trap the App newworkbook event. Problems, a bit more complex, and will happen for every new workbook. Or, if it is only to happen for new workbooks from a particular template, add workbook open code to that template. The code can be put in more meaningfully named macros, and just call those macros from the event code. -- HTH RP "Tony" wrote in message ... How to execute macros on File / New or File / Open in Excel XP ? So far I have found that if I will put the procedure named Workbook_Open in ThisWorkbook it works. But I would like to have two different actions executed on Open and on New. Should I name them Workbook_Open and Workbook_New and have them stored in ThisWorkbook ? Or is there any way to have them as a modules with some specific names ? Regards, Tony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
autoexecute macros on open or new
how about if you change the personal.xls and add whatever commands yo need to the Auto_Open? That would work -- halem ----------------------------------------------------------------------- halem2's Profile: http://www.excelforum.com/member.php...nfo&userid=993 View this thread: http://www.excelforum.com/showthread.php?threadid=26805 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
autoexecute macros on open or new
You could use an application event to look for opening an existing workbook or
creating a new workbook. Create a new workbook. hit alt-f11 find your workbook/project and rightclick on it. Insert|class module It should be called Class1 (default name). Paste this in that code window: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) MsgBox "New workbook" End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) MsgBox "Opening an existing workbook." End Sub Now find your "thisworkbook" module under the Microsoft Objects category in your project. rightclick on it and select view code. Paste this into that code window: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) MsgBox "New workbook" End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) MsgBox "Opening an existing workbook." End Sub Save this as addin (file|saveas and scroll down the save as type list). remember the location where you saved it. Then close excel. reopen excel and choose tools|addins Select your addin--you may have to browse for it. now try it out--open a workbook, start a new workbook. (You'll replace the msgboxes with meaningful code, though.) If you want to read more about application events, visit Chip Pearson's site: http://www.cpearson.com/excel/AppEvent.htm Chip also has some notes about events at: http://www.cpearson.com/excel/events.htm If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm David McRitchie also has notes at: http://www.mvps.org/dmcritchie/excel/event.htm Tony wrote: How to execute macros on File / New or File / Open in Excel XP ? So far I have found that if I will put the procedure named Workbook_Open in ThisWorkbook it works. But I would like to have two different actions executed on Open and on New. Should I name them Workbook_Open and Workbook_New and have them stored in ThisWorkbook ? Or is there any way to have them as a modules with some specific names ? Regards, Tony -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP !! Macros to autoexecute on open | Excel Discussion (Misc queries) | |||
Open workbook-macros enabled, opening another with macros | Excel Programming | |||
Macro Size Limit / open macros with macros? | Excel Programming | |||
Autoexecute Macros | Excel Programming | |||
Opening an AutoExecute Macro Workbook from an AutoExecute Macro Workbook | Excel Programming |