Can a user write a mcaro that will automatically execute when the file is
opened?
--
Allan

hi
yes. use the workbook_open evert.
Private Sub Workbook_Open()
MsgBox "Hi"
End Sub

regards
FSt1

"Flipper" wrote:

Can a user write a mcaro that will automatically execute when the file is
opened?
--
Allan

Yes. From the VBE, under the ThisWorkbook module, you can use something like
this:

Private Sub Workbook_Open()
'Call a macro previously written
Application.Run "Book1!MyMacro"

'Any other coding you want to run
End Sub


Note that there are many other events you can use to automatically activate
macros.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Flipper" wrote:

Can a user write a mcaro that will automatically execute when the file is
opened?
--
Allan

I don't understand. Where do this text string go in the event?
--
Allan


"FSt1" wrote:

hi
yes. use the workbook_open evert.
Private Sub Workbook_Open()
MsgBox "Hi"
End Sub

regards
FSt1

"Flipper" wrote:

Can a user write a mcaro that will automatically execute when the file is
opened?
--
Allan

Sorry, I am not understanding. Your verbiage "'Call a macro previously written
Application.Run "Book1!MyMacro"" waht does that mean? Do I insert the name of my Macro(s) here, or the name or my workbook? I presume that Book1!MyMacro would be the name of my macro? So does that mean Application.Run will execute it?
I'm very much a macro novice.
Thanks

--
Allan


"Luke M" wrote:

Yes. From the VBE, under the ThisWorkbook module, you can use something like
this:

Private Sub Workbook_Open()
'Call a macro previously written
Application.Run "Book1!MyMacro"

'Any other coding you want to run
End Sub


Note that there are many other events you can use to automatically activate
macros.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Flipper" wrote:

Can a user write a mcaro that will automatically execute when the file is
opened?
--
Allan

Using the Workbook_Open event, you can either write your macro here, or you
can call it using Application.Run
Technically, it makes no difference. The advantage of placing the macro
somewhere else would be if its in another workbook, or you want to be able to
call it via other means (such as a button in your workbook.)

Now, to answer your question. You have the option of simply stating the
macro's name IF you've alwasy used distinct names (don't have same name macro
in module1 and module 2.)

If your macro name is "My_Macro" is in Module1, of workbook "My book.xls"
the 3 ways of calling the macro with increasing levels of refinement:

Application.Run ("MyMacro")
Application.Run ("Module1.MyMacro")
Application.Run ("'My book.xls'!Module1.MyMacro")

Callout which module/sheet you want if you have duplicate macro names.
Callout which workbook to use if calling a macro from another open workbook,
or the possibility exists that another open workbook has same macro name.

So, altogether

Private Sub Workbook_Open
Application.Run ("MyMacro")
'some other coding
MsgBox "Hi"
End Sub

This macro will cause the MyMacro to run when opened, and will then display
a msgbox. (illustrating the different ways to accomplish same goal)




--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Flipper" wrote:

Sorry, I am not understanding. Your verbiage "'Call a macro previously written
Application.Run "Book1!MyMacro"" waht does that mean? Do I insert the name of my Macro(s) here, or the name or my workbook? I presume that Book1!MyMacro would be the name of my macro? So does that mean Application.Run will execute it?
I'm very much a macro novice.
Thanks

--
Allan


"Luke M" wrote:

Yes. From the VBE, under the ThisWorkbook module, you can use something like
this:

Private Sub Workbook_Open()
'Call a macro previously written
Application.Run "Book1!MyMacro"

'Any other coding you want to run
End Sub


Note that there are many other events you can use to automatically activate
macros.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Flipper" wrote:

Can a user write a mcaro that will automatically execute when the file is
opened?
--
Allan

Ok, thanks Luke. I did get Automatic Run to work, but it leads me to another
problem, which is that whatever worksheet that was active when I closed the
workbook is where the Automatic Run places the results of the macro, rather
than where the macro is supposed to go. As follows
The workbook contains about 20 separate worksheets. I have 2 macros, each
macro applies specifically to 1 worksheet. That, one macro performs a series
of steps where it opens another file, copies data, and then pastes that data
into that specific worksheet. When I added the Automatic Run, it pasted the
data into whatever worksheet was active when I previously closed the workbook.
--
Allan


"Luke M" wrote:

Using the Workbook_Open event, you can either write your macro here, or you
can call it using Application.Run
Technically, it makes no difference. The advantage of placing the macro
somewhere else would be if its in another workbook, or you want to be able to
call it via other means (such as a button in your workbook.)

Now, to answer your question. You have the option of simply stating the
macro's name IF you've alwasy used distinct names (don't have same name macro
in module1 and module 2.)

If your macro name is "My_Macro" is in Module1, of workbook "My book.xls"
the 3 ways of calling the macro with increasing levels of refinement:

Application.Run ("MyMacro")
Application.Run ("Module1.MyMacro")
Application.Run ("'My book.xls'!Module1.MyMacro")

Callout which module/sheet you want if you have duplicate macro names.
Callout which workbook to use if calling a macro from another open workbook,
or the possibility exists that another open workbook has same macro name.

So, altogether

Private Sub Workbook_Open
Application.Run ("MyMacro")
'some other coding
MsgBox "Hi"
End Sub

This macro will cause the MyMacro to run when opened, and will then display
a msgbox. (illustrating the different ways to accomplish same goal)




--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Flipper" wrote:

Sorry, I am not understanding. Your verbiage "'Call a macro previously written
Application.Run "Book1!MyMacro"" waht does that mean? Do I insert the name of my Macro(s) here, or the name or my workbook? I presume that Book1!MyMacro would be the name of my macro? So does that mean Application.Run will execute it?
I'm very much a macro novice.
Thanks

--
Allan


"Luke M" wrote:

Yes. From the VBE, under the ThisWorkbook module, you can use something like
this:

Private Sub Workbook_Open()
'Call a macro previously written
Application.Run "Book1!MyMacro"

'Any other coding you want to run
End Sub


Note that there are many other events you can use to automatically activate
macros.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Flipper" wrote:

Can a user write a mcaro that will automatically execute when the file is
opened?
--
Allan

You have a couple of choices.

In your workbook_open code select the sheet you want then run the macro.

sub workbook_open()
Sheets("Sheet1").select
macroname
end sub

Or select the proper sheet in your macro.


Gord Dibben MS Excel MVP

On Fri, 14 Aug 2009 14:15:03 -0700, Flipper
wrote:

Ok, thanks Luke. I did get Automatic Run to work, but it leads me to another
problem, which is that whatever worksheet that was active when I closed the
workbook is where the Automatic Run places the results of the macro, rather
than where the macro is supposed to go. As follows
The workbook contains about 20 separate worksheets. I have 2 macros, each
macro applies specifically to 1 worksheet. That, one macro performs a series
of steps where it opens another file, copies data, and then pastes that data
into that specific worksheet. When I added the Automatic Run, it pasted the
data into whatever worksheet was active when I previously closed the workbook.