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.

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.

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.

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