Macro to open multiple macros

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

Hi people,
I need a macro to open multiple macros when it is ran. Any ideas ?(by
the way, Im not very experienced with macros as you might be able to
tell).

Thanks
 
Stu, "open multiple macros"? do you want to run them from one macro? if so
lets say you have some macros like this

Sub test1()
MsgBox "this is macro test1"
End Sub

Sub test2()
MsgBox "this is macro test2"
End Sub

Sub test3()
MsgBox "this is macro test3"
End Sub

then use something like this to run them all
Sub Run_All()
test1
test2
test3
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Stu,

Am I missing something?

Sub RunAll()

macro1

macro2

macro3

'etc

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sub MasterMacro()
macro1
macro2 "abcd", 3
macro3
End sub

Sub Macro1()
msgbox "In macro1"
End Sub

Sub Macro2( sStr as String, lngNum as Long)
msgbox "In macro2 with args: " & sStr & ", " & lngNum
End Sub

Sub Macro3()
msgbox "In macro3"
End sub

So you just put the name (and arguments) of the macro you want to call/run
in the calling procedure.
 
Syntax 1 and Syntax 2 assume that the macros are either in the same
WorkBook or that there is a Reference to the WorkBook containing the
macros.

To set a reference to another WorkBook : If the WorkBook containing
the macro to run is not open, open the WorkBook. Alt-F11 to access
VBE. Ctrl-R to activate Project Explorer. Select the project of the
macro that does the called. Tools > References. Check the name of the
project that contains the called macros.

Syntax 1

Call MacroName ' No parameters
Call MacroName (param1, param2) ' Parameters

Syntax 2

MacroName ' No parameters
MacroName param1, param2 ' Parameters

Syntax 3 assumes that the WorkBook contaning the called macros is
open, but that there are no References to that WorkBook.

Application.Run "'FileName.xls'!MacroName"
' No parameters

Application.Run "'FileName.xls'!MacroName", param1, param2
' Parameters

--------------------------------------------------------------------------------------------------------------

Here are some WebSites that offer info on writing VBA code (aka
creating macros) :

http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials
http://support.microsoft.com/default.aspx?scid=/support/excel/content/vba101/default.asp


HTH
Paul
 
In that case I need a macro to run a macro on a different sheet. I have
9 sheets all containing macros and need 1 macro to run them all at
once.

Thanks
 
Stu,

Elaborate please. Have you put your macros in worksheet code modules? If so,
just move them to a normal code module. If they are worksheet event modules,
you will need to change them from their default Private, and then call with

Call Worksheets(1).macro_name

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
The macros are in:

VBA project (Filename.xls)
Microsoft excel objects
Sheet 1
Sheet 2 (Here is where the macros are(the sheets))
Etc...

Thanks
 
Stu,

So call like

Call Worksheets("Sheet1").macro1

Call Worksheets("Sheet2").macro2

But make sure they are not private.

Also, why have you put macros in a sheet module? If you want to call them
from elsewhere it is easier to put them in a normal code module
(Insert>Module).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Because I don't know how to put them in a sheet module and link it to
the sheet. But i saw sheet 1, sheet 2, etc... and thought I can put it
there because the macro will go with the sheet.

(Im new to macros so i havent a clue what im doing most of the time)

Thanks
 
When I try to run the macro which opens macros from other sheets i get
an error message saying:

Microsoft Visual Basic (In top bar)

Subscript out of range

any ideas as to why this happens?

Thanks
 
Stu,

That sounds as if you have got the name of the worksheet wrong. As I
suggested, it would be of the format

Call Worksheets("Sheet1").macro1

and make sure it is not private. But again I suggest that you put it in a
normal code module, which you can create from the Insert menu.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top