Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Running A Macro From Within Another Macro

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is there a way of calling a macro from within another macro?

Example

Sub Button1_Click()
For a = 1 To 10000
Range("A1") . Value = a
Next a
' Would now like to run the macro which is behind Sub Button2_Click()
End Sub


Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Bubble Sort
- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Delete Multiple Named Ranges Quickly
- This macro will allow you to delete multiple named ranges very quickly. When you run this macro, a message box will pop-

Similar Topics







Hello,
I really need your help.

I am trying to create a right click menu in Excel 2000 and need your help

The format would be:

Menu Option 1
- Sub Option1 "Do Macro Name"
- Sub Option2 "Do Macro Name"
- Sub Option3 "Do Macro Name"
- Sub Option4 "Do Macro Name"
- Sub Option5 "Do Macro Name"
- Sub Option6 "Do Macro Name"
- Sub Option7 "Do Macro Name"
- Sub Option8 "Do Macro Name"
- Sub Option9 "Do Macro Name"
- Sub Option10 "Do Macro Name"
- Sub Option11 "Do Macro Name"
- Sub Option12 "Do Macro Name"
- Sub Option13 "Do Macro Name"
- Sub Option14 "Do Macro Name"
- Sub Option15 "Do Macro Name"
- Sub Option16 "Do Macro Name"

Much thanks and appreciation

Jason

Hi there,
you marvelous guys provided me with this macro to call up one macro or another depending on the value in a cell. It will delete a column before running a comparison macro. It works well!.
I have now tried to use this macro to run two other macros but I get myself into a bit of a loop because in this case I am using an "add/remove button" to call a macro to
1.add columns if they arent there
or
2.delete columns if they are there

The macro below will delete the columns then add them again!!!
I can see why but not sure how to fix it..The macro deletes the columns with macro colcut... then aa4 no longer has "Prelim Dwg sent to AB" so it calls macro pastcol and puts them back in!

Any help appreciated

Simon



Code:

sub cut_past_col
If StrComp(Range("AA4"), "Prelim Dwg sent to AB", vbTextCompare) = 0 Then Call colcut
  Call pastcol
end sub





Hi guys,

I wonder if you anyone could help me?

In a worksheet I run a macro called do_each_x_seconds Code:

Sub Do_each_x_seconds()
 
SchedRecalc = Now + TimeValue("00:00:01")
       Range("Y110") = "Running"
       Application.OnTime SchedRecalc, "Sheet2.Update_val"

End sub


This macro will coninue running every one second (in this case) forever.

Is there a way that I can command from another macro to stop this one working?

many thanks in advance,

John


I am trying to call another macro plot within a main macro that chooses data.
I define a range "plotvalues" in the main macro and the second macro plots
"plotvalues." However, the variable is not being recognized. here is the
gist of what my code is like.

Sub datasplit()
....
plotvalues = ActiveSheet.Range("A1:L15").Address

Macroplot
End Sub()

Sub Macroplot()
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Input").Range(plotvalues),
PlotBy:= _ xlColumns

End Sub()

Please help!




Hi,

I am facing the following problem. I have setup a macro in a file and, with slight changes, replicated this for 4 different files. So each workbook has the same macro pretty much.

Two of the workbooks refer to the same "line" and each one works on a different "direction" (south/north for example).

What I did, for matter of convenience is to create another workbook and place some nice buttons that when you click one of them it will open two of the workbooks and run the macro that is included inside them.

The problem is, this takes forever!! When I open each workbook individually and run their macros they take a reasonable amount of time, whenever I attempt to open them through another workbook and call the macros it takes forever!. I tried calling only one workbook and running 1 macro but it still takes much longer than opening the file manually and running the macro manually from within the file.

Any ideas why that happens? I can post the code if needed but it's nothing special, just opening the xls and running the macro.


I'm using the macro below Kindly sent to me by Bob Phillips, to print out
when in press a button in excel, It works great except it resets to zero
before it prints is there a way to get it to print the value in AA2 before it
resets it back to zero

Sub Button2_Click()
With Sheets("Sheet1")
.Range("AB3").Value = _
.Range("AB3").Value + .Range("AA2").Value
.Range("AA2").Value = 0
End With
ActiveSheet.Range("AG3:AH10").PrintOut preview:=False
End Sub

Thanks



Hey,

I have this workbook that has 2 macros that are running simultaneously and I'm looking to run macro 1, then macro 2

The one macro will run if there are any changes made to the worksheet (a message will pop up if the sum of values (=A1+B1; =A2+B2;...) in a column (C) is greater than 0)

Another macro will change the original values (in column B)

While running the 2nd macro, value B1 will change, then the 1st macro will run saying, "Values in C2, C5, C6 are greater than zero". After pushing "OK", the 2nd macro will continue and fill in the value for B2, then the 1st macro will run again, saying "Values in C5, C6 are greater than zero"......

Is there a way to have the 2nd macro run fully through it's cycle, and then have the 1st macro run afterwards???


Thank you and I appreciate your time and efforts,
Bob


I am calling the following macro from within another macro. When the macro reaches to syntax to call for the following macro, I receive a compile error message. How do I fix?

Sub SolverSyn()
'
' SolverSyn Macro
' Macro recorded 11/2/2009 by
'

'
SolverOk SetCell:="$J$19", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$16:$G$17"
SolverSolve
End Sub

Thanks.


Hi all,

I am having trouble running a macro.

Everytime I click to run it, it runs another macro instead.

Originally they had similar names, "drag_formula" and "drag_formula1", so I thought that maybe why it is getting confused. But i have now changed the name of the macro I am trying to run from "drag_formula1" to "Part2", but it is still running the wrong macro!

I've tried closing down and reopening but i'm still faced with the same problem.

Does anyone have any ideas?

Nicko



I run a macro in Wkbk1, the macro uses the Application.Run command to
call a macro in Wkbk2.

The second macro closes Wkbk1. As soon as Wkbk1 is closed the macro
stops running.

Is there a way to stop this from happening?

Thanks in advance




I have the following macro that I am trying to adapt to work with my user
form. The user form is to prompt for a password to run a macro that unhides
a range of cells. I have not a clue what I am doing, but I am trying. Any
help would be greatly appreciated.

Sub Button1_Click()
If TextBox1.Text <> "1234" Then
MsgBox "Invalid password - you cannot access this"
UnHide.Hide
Exit Sub
Else
Range("E17:E50").Select
Selection.Font.ColorIndex = 0
Range("E17").Select
ActiveSheet.Range("A1").Select

End If
End Sub

Thanks
Mike Rogers



Hi All,

Thanks in advance for any help you can offer.

I have a situation where I am trying to run a macro in a different workbook using VBA. I want the macro to run within the workbook in which it sits. The following works:

Code:

    Application.Run "'Freed Blogs'!Main"


However it tries to run the macro main on the book I am calling it from when I want it to run on the woorkbook in which it resides. To add to my problems I ABSOLUTELY CAN NOT TOUCH the code in the other book as it belongs to another department. They have not specified any object variables so it means the macro is looking at my sheet.

Does Run have any parameters that you can force the macro to run in it's own book?

Is there another way I can do this?

Many thanks,

Fred!




Hi All

I have written many Macro's and I wish to use the "CALL" function within some of the macro's.

Can anyone offer some advise please..


Below is a sample of the Macro..

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



Please Login or Register  to view this content.


>>>>>>>>>>>>>>>>>>>>>>


Peter

Hi I have an interesting problem (I think at least). I have two macros. The first one gets a file name and worksheet name. Then it calls the second macro which opens up notepad (with the specified file name) and pastes the information in excel. The problem that occurs is when I call the second macro more then once in a row from the first macro. It runs great the first time but then problems occur as it tries to run the macro again. (Some times it just closes my excel file with prompting me to save). Calling any of the files works on an individual basis (I've tried for all three). I've erased some of the code due to privacy issues but path location is identical for all three files. Here is the code.

Code:

Sub Update()

Call Notepad("survey_fistp_en", "FISTP Data")
Call Notepad("mco_csrm_en", "MCO Data")
Call Notepad("survey_manager_en", "Manager Data")

End Sub

Sub Notepad(Name_of_File, Name_of_Worksheet)

RetVal = Shell("C:\WINDOWS\notepad.exe \\[Path location]" & Name_of_File & ".Asc ", 1)
SendKeys "^a", 10000
SendKeys "^c", 10000
SendKeys "%{F4}{TAB}~"
Workbooks("TLC Comment Reports").Worksheets(Name_of_Worksheet).Range("A5").PasteSpecial Paste:=xlPasteAll

End Sub







Hi

How do you stop a macro running on a workbook that has already had the macro run on it? So for example, I open a workbook, run the macro. I then open another workbook, run the macro on the newly opened workbook. The macro is run on both workbooks which results in the first opened workbook having any newly added tables etc appearing twice. Hope this makes sense :/

My code is in work but I have something like:

For Each workbook in Workbook

For Each ws in workbook.Worksheets

If ws.name = "Sheet1" Then

"Do Something"

End If

If ws.name = "Sheet2" Then

"Do Something"
End If

Next ws

Next workbook

I was thinking of putting some text in a cell that isn't immediately visible when a macro is first run, then when the macro is run for the second time it looks for this text and if it is present the macro stops running on that particular workbook and then moves to the next workbook that is open in the same instance of Excel. I'm not sure how best to do it if at all it is possible?

Any help would be greatly appreciated. Thanks in advance.

I need to create a macro to simulate 10000 binary vectors where marginal probabilities p1=.01 and p2=.04 and correlation=.27. I have no problems simulating the vectors using the random number generator but I have problems ensuring that the correlation is 0.27. This macro looks okay to me but it runs forever. Do I need to use Cholesky to simulate the binary vectors?


Code:

Dim f As Double

f = 1

While (f < 0.00265 Or f > 0.00275)

    Cells.Select

    Range("A1:G10000").Activate

    Selection.ClearContents

     Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$A$1:$A$10000"), _

        1, 10000, 3, , 0.01

     Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$B$1:$B$10000"), _

        1, 10000, 3, , 0.04

     Application.Run "ATPVBAEN.XLA!Mcorrel", ActiveSheet.Range("$A$1:$B$10000") _

        , ActiveSheet.Range("$D$1"), "C", False

        f = ActiveSheet.Range("$E$3")

Wend    

End Sub





Hi,

I am trying to setup procedures that perform some steps then run
whichever macro is passed to them as a string argument

the actual code leaving the STRING variable containing the macro and
its arguments blank is:

Sub SendMacroName()

UseMacroStringToRunMacro STRING

End Sub

Sub UseStringToRunMacro(STRING)

....PERFORMS SOME STEPS...

'runs the macro represented in STRING
Application.Run STRING

End Sub

This works fine in all but one case: when the macro to be run is in
another file and has arguments. the actual string value that would work
in such a case:

Application.Run "FILE CONTAINING MACRO TO RUN.xls!MACRO TO RUN",
"ARGUMENT 1", "ARGUMENT 2", etc

i can give STRING that exact value by either of the following code, but
neither work on the Application.Run STRING command

STRING = """FILE.xls!MACRO""" & ", " & """ARGUMENT 1""" etc
or
STRING = """FILE.xls!MACRO"", ""ARGUMENT 1"" etc

both result in the error message: method 'run' of object application
failed


if the macro to run were in the same file as the macro calling it
[UseStringToRunMacro] the following would work:

STRING = "'MACRO_NO_FILEPATH, "ARGUMENT 1", ETC '"

[notice the string is bracketed by single quotes]
but if the macro is in another file this doesn't work [and it is NOT
b/c the file containing the macro isn't referenced, i tried
"'FILE.xls!MACRO, "ARGUMENT 1", ETC '" [that produces a macro cannot be
found error]

if the macro to run were in another file but had no arguments the
following works:

STRING = "FILE.xls!MACRO"

my guess is it has something to do with extra quotation marks around
the string, or how to represent the file name when the macro has
arguments. i think what may be happening is the STRING is interpreted
as being the macro to run not as encompassing the name of the macro and
each of its arguments.

Thanks for your help,

Ian




i have a macro that read a workbook of 10000 line. it reads one by one row and then do some arithmetic and then append a new data in a new column in the workbook. When i run the macro, it takes about 30 mins to run. the excel 'freezes' when the macro is being ran but i know in background the macro is looping in all the rows. i have in my macro too Application.screenupdating=false

How can i put some sort of indication of what line the macro is manipulating without slowing down the macro? can i put a msg in the status bar? like row 1 being done, row 2 being done etc....

i want to know if i look at the excel after 15mins, i know which row the macro has reached so that i have an indication when macro will be completed.


I've run accross a problem where I would like to be able to refrence
data i've named in a macro that i'm running within a macro either by
having the macro output into the second macro or refrence a name in a
macro, is this possible?

Sub Macro1()


book = Range("B10")
booknum = 1
Path = Range("B11")
Sheets("data").Select
If Range("A52") <> "" Then
Application.Run "JEUpload_Template.xls!CreateSheet"
Range("A1:L1") = Heading
Range("A2:L51") = Sheet02
Sheets("Sheet2").Select
Sheets("Sheet2").Move

ChDir Path
ActiveWorkbook.SaveAs Filename:= _
book & booknum, FileFormat:=xlText, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
booknum = booknum + 1
End If

End Sub




I have a Userform which posts data to another WorkBook.( A network sheet) A macro on that workbook runs after being called from the Userform
Private Sub cmbDataEntry_Click()

What I'm trying to do is to have a macro that's running on the remote sheet to call a macro that's in the Userform that originally made the call to the remote sheet.

I tried calling the macro on the userform like this.

Application.Run "'Monday-form.xls'!Private Sub AfterEntryCleanUp"

This macro never gets called.

Do I have to include the name of the Userform XForm2010. If so where and how?
What is the correct format for calling a macro on a Userform? This macro clears all the entries / textboxes etc..etc.. on the Userform. I cant do it before I execute the code on the network sheet, because the values are still needed from the Userform.

Thanks


Hi everyone,
I tried the following macro, self explanatory I would have thought, but the row is deleted whether the cell matches or not. Any ideas, please ? Quote:

Sub Button1_Click()
If a1 = a6 Then
Range("a6").Select
Selection.EntireRow.Delete
End If


End Sub




Hi all,

Am using the program where am calling the other macro and after that macro and based on the result i need to run the remaining steps of macro1

Sub macro1()

'some code here

Call step2 'calling another macro

'some code here

End Sub

My problem is the step2 macro takes some 20 seconds to finish the run. so i want to resume the macro1 for 20 seconds and then start running the secong part of code.

Can anyone help me to solve this issue....
thanks in advance....


I've got a macro working that updates things in different sheets, the sheets have peoples names in (this can't be changed).

When running the macro its fine, but if a new person is added i would need to update the macro for every sheet (the macro can be run from every sheet within the workbook).

I've noticed if you right click the sheet button and go to properties (or something) that it shows (Name) Sheet2 and Name Joe Blogs.

Instead of the macro calling to Name i want it to call to (Name), is this possible?

The code that calls the sheet looks something like this:

Sheets.("Joe Blogs")

Instead of looking for Joe Blogs i want it to look for Sheet2

Is this possible or am i just wishing on a dream?


I'm new to macros, and have created them, so far, only through the
"record macro" function. My problem macro performs a "save as" and then
(supposedly) closes the sheet. When running the macro I find I need to
manually intervene twice, once for the "file exists - overwrite?"
dialog, and again for the "save changes to?" dialog. Is there a way, in
the macro, for me to respond to these queries? I responded during the
"record" but it didn't seem to take.


Thanks.

JLOB



Hi,

I am writing a VB macro to format a large amount of data in MS Excel
2000.

Part of the macro uses the 'Text to Columns' function, however, when
running the macro the prompt "Do you want to replace the contents of
the destination cells?" appears when the macro gets to the 'Text to
Columns' command in the code.

Is there a way that I can write the macro to always select 'OK' at this
point and keep running.

Thanks in advance, any help is appreciated.