Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a VBA Macro when Excel Opens
Hi folks,
I have a macro that I wrote in VBA that I want to open as soon as I open Excel (it's part of a bigger process and ideally we don't want to have to be there to kick off this Macro......we have a way to open Excel automatically as part of the process). I can get the worksheet to open as Excel opens alright by placing it in the 'Xlstart' folder under my profile. I have read 2 different ways for me to do it also: 1. Name the macro Auto_Run. This doesnt do anything for me. 2. Put the code into the 'workbook_open' routine of the 'ThisWorkbook' method. When I try this I get errors, something to do with how I have placed the code in I think. My Macro code has the following format: Option explicit Dim AllMyVariables ------------------------------------- Sub format 'Some code' Call Sub_a 'Some more code' Call Sub_b 'Even more code' End Sub ------------------------------------- sub Sub_a end sub ------------------------------------- sub Sub_b end sub ------------------------------------ I have put a msg box before the call function in the open routine but it skips the msgbox and goes straight to 'Call Format' highlights it and gives me an error: "Compile Error: Argument not optional" when I click ok it highlights Private Sub Workbook_Open in yellow. I have also renamed the main sub to 'code' in case the word Format was reserved and it did the same thing except the error was "Sub or function not defined" I'm working on Excel 2000 if that's important info and I have Macro security set to Low. Thanks a million. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a VBA Macro when Excel Opens
The second method is how I would implement the desired result you describe.
As for errors, it really depends on what you are asking the program to do, and whether the appropriate "structure" is set up to perform what you are asking. But the second approach works, provided you don't have errors in your code. Maybe posting the code you want run on opening the workbook would get others to help fix the problems. "mlocmcgash" wrote: Hi folks, I have a macro that I wrote in VBA that I want to open as soon as I open Excel (it's part of a bigger process and ideally we don't want to have to be there to kick off this Macro......we have a way to open Excel automatically as part of the process). I can get the worksheet to open as Excel opens alright by placing it in the 'Xlstart' folder under my profile. I have read 2 different ways for me to do it also: 1. Name the macro Auto_Run. This doesnt do anything for me. 2. Put the code into the 'workbook_open' routine of the 'ThisWorkbook' method. When I try this I get errors, something to do with how I have placed the code in I think. My Macro code has the following format: Option explicit Dim AllMyVariables ------------------------------------- Sub format 'Some code' Call Sub_a 'Some more code' Call Sub_b 'Even more code' End Sub ------------------------------------- sub Sub_a end sub ------------------------------------- sub Sub_b end sub ------------------------------------ I have put a msg box before the call function in the open routine but it skips the msgbox and goes straight to 'Call Format' highlights it and gives me an error: "Compile Error: Argument not optional" when I click ok it highlights Private Sub Workbook_Open in yellow. I have also renamed the main sub to 'code' in case the word Format was reserved and it did the same thing except the error was "Sub or function not defined" I'm working on Excel 2000 if that's important info and I have Macro security set to Low. Thanks a million. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a VBA Macro when Excel Opens
Got the 1st method to work finally.
Just started recording a macro, went to 'Tools' 'Macro' 'Macros' then selected my Macro and pressed 'Run'. Then I stopped recording the Macro. Not done programatically but works none the less. "GB" wrote: The second method is how I would implement the desired result you describe. As for errors, it really depends on what you are asking the program to do, and whether the appropriate "structure" is set up to perform what you are asking. But the second approach works, provided you don't have errors in your code. Maybe posting the code you want run on opening the workbook would get others to help fix the problems. "mlocmcgash" wrote: Hi folks, I have a macro that I wrote in VBA that I want to open as soon as I open Excel (it's part of a bigger process and ideally we don't want to have to be there to kick off this Macro......we have a way to open Excel automatically as part of the process). I can get the worksheet to open as Excel opens alright by placing it in the 'Xlstart' folder under my profile. I have read 2 different ways for me to do it also: 1. Name the macro Auto_Run. This doesnt do anything for me. 2. Put the code into the 'workbook_open' routine of the 'ThisWorkbook' method. When I try this I get errors, something to do with how I have placed the code in I think. My Macro code has the following format: Option explicit Dim AllMyVariables ------------------------------------- Sub format 'Some code' Call Sub_a 'Some more code' Call Sub_b 'Even more code' End Sub ------------------------------------- sub Sub_a end sub ------------------------------------- sub Sub_b end sub ------------------------------------ I have put a msg box before the call function in the open routine but it skips the msgbox and goes straight to 'Call Format' highlights it and gives me an error: "Compile Error: Argument not optional" when I click ok it highlights Private Sub Workbook_Open in yellow. I have also renamed the main sub to 'code' in case the word Format was reserved and it did the same thing except the error was "Sub or function not defined" I'm working on Excel 2000 if that's important info and I have Macro security set to Low. Thanks a million. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a VBA Macro when Excel Opens
Not Auto_run.
Try Auto_Open And Format is a function in VBA that works like the worksheet function =text(). msgbox format(123.23323,"000.0") mlocmcgash wrote: Hi folks, I have a macro that I wrote in VBA that I want to open as soon as I open Excel (it's part of a bigger process and ideally we don't want to have to be there to kick off this Macro......we have a way to open Excel automatically as part of the process). I can get the worksheet to open as Excel opens alright by placing it in the 'Xlstart' folder under my profile. I have read 2 different ways for me to do it also: 1. Name the macro Auto_Run. This doesnt do anything for me. 2. Put the code into the 'workbook_open' routine of the 'ThisWorkbook' method. When I try this I get errors, something to do with how I have placed the code in I think. My Macro code has the following format: Option explicit Dim AllMyVariables ------------------------------------- Sub format 'Some code' Call Sub_a 'Some more code' Call Sub_b 'Even more code' End Sub ------------------------------------- sub Sub_a end sub ------------------------------------- sub Sub_b end sub ------------------------------------ I have put a msg box before the call function in the open routine but it skips the msgbox and goes straight to 'Call Format' highlights it and gives me an error: "Compile Error: Argument not optional" when I click ok it highlights Private Sub Workbook_Open in yellow. I have also renamed the main sub to 'code' in case the word Format was reserved and it did the same thing except the error was "Sub or function not defined" I'm working on Excel 2000 if that's important info and I have Macro security set to Low. Thanks a million. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
Excel opens but does not open file | Excel Discussion (Misc queries) | |||
Excel opens but workbook does not open | Excel Discussion (Misc queries) | |||
When I open EXCEL my PERSONAL.XLS opens as well !! | New Users to Excel | |||
OPEN WORKBOOK WHEN EXCEL OPENS | Excel Programming |