F
Flipper
Can a user write a mcaro that will automatically execute when the file is
opened?
opened?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
FSt1
hi
yes. use the workbook_open evert.
Private Sub Workbook_Open()
MsgBox "Hi"
End Sub
regards
FSt1
yes. use the workbook_open evert.
Private Sub Workbook_Open()
MsgBox "Hi"
End Sub
regards
FSt1
L
Luke M
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.
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.
F
Flipper
I don't understand. Where do this text string go in the event?
F
Flipper
Sorry, I am not understanding. Your verbiage "'Call a macro previously written
Thanks
I'm very much a macro novice.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?
Thanks
L
Luke M
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)
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)
F
Flipper
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.
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.
G
Gord Dibben
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
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