Click once

[Sheree]

Hi Guys,

I would like to click once and 5 macros go into action, how do I do this?

I can't put the macros together because they don't run the way I like.

Sheree

 

[Smitty]

You can use something like this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">With</SPAN> Application
        .Run "Macro1"
        .Run "Macro2"
        .Run "Macro3"
        .Run "Macro4"
        .Run "Macro5"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[tactps]

Add in this macro (which is the one you will attach to the button)

Sub Run Macros()
MacroOne
MacroTwo
End sub

Sub MacroOne()
[Your macro here]
End Sub

etc

In other words, just type in the names of the macros. When it exits macro1 it will return to this sub and run macro 2.

It is an effective way of writing macros (separate into its parts) as it makes it easier to modify or remove parts later.

 

[cstlaurent]

create five buton, assign a macro to each ot them. and record a macro clicking on it.

Or use VBA

docmd.runmacro "1"
docmd.runmacro "2"

and so. ans so

 

[Sheree]

I just tried that way and all the macros worked on one page of my doc,I need the macros to run on different sheets with in a the doc.

 

[Smitty]

How about:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    Application.Run "Macro1"
        Sheets("Sheet2").Activate
    Application.Run "Macro2"
        Sheets("Sheet3").Activate
    Application.Run "Macro3"
        Sheets("Sheet4").Activate
    Application.Run "Macro4"
        Sheets("Sheet5").Activate
    Application.Run "Macro5"
        Sheets("Sheet6").Activate
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Smitty

 

[Sheree]

I tried to record a macro by running the four macros but I lost information on my last excel sheet....

 

[tactps]

Or add into your macros:
For each Worksheet in Workbook....

 

[Sheree]

Hi PennySaver,
I did it that way and lost information.

 

[Sheree]

Hi tactps,

what code do I put into the macros