Free Microsoft Excel 2013 Quick Reference

How to call a macro from other workbook?

hi, due to some reason, i need to call a macro from other workbook. Can anyone kindly show me an example how to do it?

Thank .


Post your answer or comment

comments powered by Disqus
does anybody know how to call a macro (that has been recorded in excel) in a vb procedure?

the macro is called : "get_Cheapest"

and it is to be run when:

Private Sub Find_Click()

code goes here... how do i call it?? (it needs to be called directly from here as there are other things happening when clicking...)

End Sub

How do we do that?

Cheers

olo

Hello Experts,

Is there a way to prevent a macro from one workbook to execute in another workbook? For example, I have "Macro1" recorded in workbook A. Can I block this macro not to execute in workbook B?

Thanks,

Yuki

Hello,

can anybody tell me how to use a macro from another excel sheet.
Situation :

I have 1 file called records.xls
This file has to be overwritten every time when filled (export from access).
When this opens I have to start an Excel macro.
How can I use or start a macro which is in my other file : macro.xls (empty
sheet with just a macro in it).
Or any other solution is welcome.

Jeroen

Take It, It Can't be done..?
Thanx anyway.
| From: "Andrew" <NoToSPAM@home>
| Subject: How to close forms opened from other workbooks...?
| Date: Sun 12 June 2005 20:22
|
| I have a form that is loaded(show) from Personal.xls.
| Other workbooks can then be opened.
|
| Q. how is it possible to check if the form (Main.FRM) is open and close it
| or reference a listbox item if it is open.
|
| Is there a way of:
| If Userforms("Main") then
| ThisTextbox.text=Workbooks("Personal").Main.TextBox1.Text
| end if
|
| Thanks for any Help.
| Andrew

From: "Andrew" <NoToSPAM@home>
Subject: How to close forms opened from other workbooks...?
Date: Sun 12 June 2005 20:22

I have a form that is loaded(show) from Personal.xls.
Other workbooks can then be opened.

Q. how is it possible to check if the form (Main.FRM) is open and close it
or reference a listbox item if it is open.

Is there a way of:
If Userforms("Main") then
ThisTextbox.text=Workbooks("Personal").Main.TextBox1.Text
end if

Thanks for any Help.
Andrew

I am currently copying a sheet from one workbook to another workbook via macro but sometimes the sheet I am coping is not updated. I would like to be able to open the second workbook, run the macro for that sheet, the macro is contained within the sheet and not a module, and then copy the sheet over.

The two main issues I have is that I do not know how to call a macro from another workbook and I do not know how to reference a macro within a sheet and not a module.

Hi everyone
I'm trying to figure out how to activate a macro from combo box or list box.
But no success.

I have a list of names:
AAA
BBB
CCC
111
222
333

That I can view through the combo box.

I have created a list of macros, that carry the same names,

AAA
BBB
CCC
111
222
333

The Q is: how can I link each name to its own macro?
so when chosen, will activate the macro?

Thanks for the help

Hello,

This is an XLA newbie question (for Excel 2000)!

I created an Excel addin (.xla type) with a macro in it and I
installed the add-in. Then I opened another workbook, activated my VBA
form and yes, there it was, my add-in.

But now I want to create a macro in the workbook that calls a macro
from the add-in (with parameters). How can I do that? My end goal of
this is to create an add-in as a subroutine and function library for
an extensive set of Excel sheets that need to be developed.

Thanks for any help!

I have two excel reports. One excel Report named as Report1.xlsb, contains a button called "Apply Filters". I want to click that button by writing a macro in another workbook. I tried to click the button by using the name used in the macros available in Report1.xlsb.
I have tried with below code. But its not working. Its throwing below error
Windows("Report1.xlsb").Activate
Application.EnableEvents = False
Application.Run "Forecast Operational Report ES.xlsb'!test'"
Run ApplyFilters_click
Please advise me if i missed something.

When I run a module in Workbook1, I want to call a Sub from a module in Workbook2.

Thanks for the help!

I have a workbook that contains a macro I need to run on a large number of
other workbooks. Is it possible to play a macro from another workbook on the
current workbook with out copying either the data or the macro manually?

I've found that if I open the workbook containing the macro, and then open
one of the "target" workbooks, I can open the Run Macro dialog box in the
target workbook and all macros in both workbooks are displayed if "All Open
Workbooks" is selected in the "Macros in:" field on the Macro dialog.
However if I select the macro I need to run from the "source" workbook, the
macro only runs on it's workbook, not the "target".

Without copying and pasting the data from the target workbooks into the
source workbook and then running the macro, is there a simpler way?

Thanks for any help

I need to be able to call a macro that is located within another file. It's no problem to call another macro that is within the same file, but as my project grows, it becomes too large and cumbersome. I need to find some way to call a macro within another file.
Say, for example I have two workbooks (FileA and FileB). Within FileA I have a macro (MacroA). At some point within that code, I need to call a macro located in FileB named MacroB.

Is this possible?
Is it possible to do with VB Script?

TIA,
DejaVu

I have a workbook that contains a macro I need to run on a large number of
other workbooks. Is it possible to play a macro from another workbook on the
current workbook with out copying either the data or the macro manually?

I've found that if I open the workbook containing the macro, and then open
one of the "target" workbooks, I can open the Run Macro dialog box in the
target workbook and all macros in both workbooks are displayed if "All Open
Workbooks" is selected in the "Macros in:" field on the Macro dialog.
However if I select the macro I need to run from the "source" workbook, the
macro only runs on it's workbook, not the "target".

Without copying and pasting the data from the target workbooks into the
source workbook and then running the macro, is there a simpler way?

Thanks for any help

Hello Experts,

Is there a way to call a macro when the cell value change? For example, if cell value is 1 then it will automatically play Macro 1. Is that possible? Please help me with the VBA codes.

Thank you,

Yuki

Hi,

Is it possible to trigger a macro from a change in a range of formula cells if that range is NOT on the active sheet?

Basically, I have a dynamic range of dates on a calculation sheet where the dates can change or dates can be added/deleted, and every time there is any change in that range I need to fire a macro. I found some code that will trigger based on a formula result, but the range had to be on the active sheet. Is there code that can do this?

Thanks,

Lawrence

Hi

I have a C program that prints to a text file a VB macro that does excel
automation. To run this macro, I need to start an excel app (i.e by going to
START->RUN-->EXCEL ) and then click on TOOLS->MACRO->CREATE NEW MACRO, supply
a name and then copy the contents of the text file (which is output of the
..exe ) and then run the macro. The macro now, operates on a excel file and
saves and closes it. " All this is working fine. "

Is there a way to automtate this task, so no user intervention is required.
Ideally, what I want is:
(1) Launch my C based program , ie. Execute the application
(2) This will now, Create the macro and also RUN it.

My OBJECTIVE In short is:
How do I run a macro from C program ?

Preferably, I would not like to use C# or .NET. my application is failry
simple; and also, I am not not a C# or .NET developer. I have seen some
articles on MSDN but they are not really helpful.
If someone can send me or tell me how to launch a macro from C, that would
be great!
Please bear in mind that I have "NO" knowledge of VB or C#. Some MSDN
examples declare vairalbes that I dont understand at all.

Hi,
I have excel having a 5 command buttons on a userform. When I press each buttons , will take you to correcsponding userform. However Iam facing a issue to call a userform which is in another workbook. I have tried to call thru application.run command .However the issue is that though it brings the requied form , When I click EXIT button on the screen , it does not bring back the control to main menu(Where 5 menu buttons are aailable)

Help is required to get a framework like how to call a userform in another workbook(Is application.run is the correct way?)
2- close the second speadhseet(where the external userform) after executing the functionality
3- come back to mainmenu

Any help would be appreciated.

Joe

I have a macro that I have been calling from a form without issue. I have created an add-in that creates a new menu. When trying to execute the macro from the menu I receive an error message that states "The macro 'OpenCaseReport(1)' cannot be found." All other macros run without issue. I am guessing it has to do with how I am calling. Need some HELP!

The cmdbarmacro to create the menu is...
Code:
Sub AddMenus()
    Dim cMenu1 As CommandBarControl
    Dim cbMainMenuBar As CommandBar
    Dim iHelpMenu As Integer
    Dim cbcCustomMenu As CommandBarControl
 
     ' (1)  Delete any existing one
     ' We must use On  Error Resume Next in   case it does not exist
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("&Cox Reporting").Delete
    On Error GoTo 0
 
     ' (2) Set a CommandBar  variable to  Worksheet menu bar
    Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
 
     ' (3) Return the Index number of the Help menu
     ' We can then use this to place a custom menu before the Help menu
    iHelpMenu = cbMainMenuBar.Controls("Help").Index
     ' Add a Control to the "Worksheet Menu Bar" BEFORE the Help menu
     ' And set a Control (CommandBarControl) variable to it
    Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, before:=iHelpMenu)
     ' Give the CommandBarControl a caption
    cbcCustomMenu.Caption = "&Cox Reporting"
    With cbcCustomMenu
        With .Controls.Add(Type:=msoControlPopup)
            .Caption = "Open SR Report"
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "2618 - IT"
                    .OnAction = "OpenCaseReport(1)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4472 - BTS"
                    .OnAction = "OpenCaseReport(2)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4473 - Las Vegas"
                    .OnAction = "OpenCaseReport(3)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4474 - Phoenix"
                    .OnAction = "OpenCaseReport(4)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4475 - 7600"
                    .OnAction = "OpenCaseReport(5)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4476 - Ent."
                    .OnAction = "OpenCaseReport(6)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4477 - Misc."
                    .OnAction = "OpenCaseReport(7)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4488 - CMTS"
                    .OnAction = "OpenCaseReport(8)"
                End With
                With .Controls.Add(Type:=msoControlButton)
                    .Caption = "4770 - IPCC"
                    .OnAction = "OpenCaseReport(9)"
                End With
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Format Case List"
            .OnAction = "FormatSR"
        End With
        With .Controls.Add(Type:=msoControlPopup)
            .Caption = "Update"
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Open"
                .OnAction = "OpenUpdate"
            End With
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Closed"
                .OnAction = "ClosedUpdate"
            End With
        End With
        With .Controls.Add(Type:=msoControlPopup)
            .Caption = "Miscellaneous "
            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Conditional Row Deletion"
                .OnAction = "DeleteRows"
            End With
        End With
    End With
 
End Sub
And the macro I am trying to call is...
Code:
Sub OpenCaseReport(s As Integer)
        Dim LSearchRow As Integer
        Dim LCopyToRow As Integer
        Dim word As String
'        On Error GoTo Err_Execute
 
    Select Case s
    Case 1
    word = "2618"
    Case 2
    word = "4472"
    Case 3
    word = "4473"
    Case 4
    word = "4474"
    Case 5
    word = "4475"
    Case 6
    word = "4476"
    Case 7
    word = "4477"
    Case 8
    word = "4488"
    Case 9
    word = "4770"
    End Select
 
    Application.ScreenUpdating = False
 
    StartWorkbook = ActiveWorkbook.Name
    StartSheet = ActiveSheet.Name
    Workbooks.Add
    ActiveSheet.Name = "Open SR Report"
        Application.DisplayAlerts = False
        Sheets("Sheet2").Select
        ActiveSheet.Delete
        Sheets("Sheet3").Select
        ActiveSheet.Delete
        Application.DisplayAlerts = True
    NewWorkbook = ActiveWorkbook.Name
    Workbooks(StartWorkbook).Activate
    Sheets(StartSheet).Select
 
        'Start search in row 4
        LSearchRow = 4
        'Start copying data to row 2 in Sheet2 (row counter variable)
        LCopyToRow = 3
        While Len(Range("A" & CStr(LSearchRow)).Value) > 0
        If Range("C" & CStr(LSearchRow)).Value  "Closed" Then
            'If value in column E = the selected portfolio, copy entire row to Sheet2
            If Range("J" & CStr(LSearchRow)).Value = word Then
                'Select row in Sheet to copy
                Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
                Selection.Copy
                'Paste row into Sheet in next row
                Workbooks(NewWorkbook).Activate
                Sheets("Open SR Report").Select
                Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                ActiveSheet.Paste
                Workbooks(StartWorkbook).Activate
                'Move counter to next row
                LCopyToRow = LCopyToRow + 1
                'Go back to continue searching
                Sheets(StartSheet).Select
            End If
          End If
            LSearchRow = LSearchRow + 1
        Wend
        Rows("3:3").Copy
        Workbooks(NewWorkbook).Activate
        Rows("2:2").Select
        ActiveSheet.Paste
        Columns("D:F").ColumnWidth = 2.14
        Range("E:F,H:H,J:L,N:N,V:V,AE:AG,AJ:AK,AM:AN,AP:AP").EntireColumn.Hidden = True
        Range("U2,Y2,AH2").ClearComments
        Rows("1:1").RowHeight = 61.5
        Rows("2:2").EntireRow.AutoFit
        Rows("2:2").Select
        With Selection
            .VerticalAlignment = xlCenter
        End With
        With Selection.Font
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
        End With
        ActiveWindow.DisplayGridlines = False
        Columns("B:B").ColumnWidth = 8
        Columns("C:C").ColumnWidth = 9
        Columns("I:I").ColumnWidth = 9
        Columns("P:P").ColumnWidth = 11
        Columns("Q:Q").ColumnWidth = 25.86
        Columns("U:U").ColumnWidth = 50
        Columns("T:T").ColumnWidth = 25
        Cells.Select
        Selection.FormatConditions.Delete
        Selection.Interior.ColorIndex = xlNone
        Workbooks(StartWorkbook).Activate
        Sheet6.Visible = xlSheetVisible
        Sheets("logos").Select
        ActiveSheet.Shapes.Range(Array("Picture 2", "Text Box 1", "Picture 3")).Select
        Selection.Copy
        Sheet6.Visible = xlSheetVeryHidden
        Sheets("SRs").Select
        Workbooks(NewWorkbook).Activate
        Range("A1").Select
        ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 50.25
    Selection.ShapeRange.IncrementTop -24.75
    Selection.ShapeRange.IncrementLeft 0.75
    Selection.ShapeRange.IncrementTop -0.75
        Range("B1:S1").Select
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = 41
            End With
        Range("A2:AO2").Select
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlDot
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        Range("B3").Select
        ActiveWindow.FreezePanes = True
        Range("A1").Select
 
    Application.ScreenUpdating = True
 
        MsgBox "Your Open SR Report is complete."
 
        Exit Sub
' Err_Execute:
'        MsgBox "An error occurred."
    End Sub


I'm hoping someone can help me with some suggestions for how to do this. I've been hired to restructure and setup all these spreadsheets and formulas, and I'm kind of stuck.*

So, I have multiple consultants and their monthly stats spreadsheets I've set up.

I need to create combined monthly, quarterly and yearly spreadsheets that are updated automatically. I can manage to do that with the workbooks I've already created, but next month, there will be a new workbook for each consultant from the template I made.

How can I make the combined stat workbook read the data from the future workbooks automatically? It must be possible.

So far, I've created a separate folder for each consultant and I'm hoping there is a way to create a formula or script that will just pull the required data from every workbook in that folder (every workbook is uniform, so it's just a matter of reading the last sheet and certain cells and pulling that info).

If there is a tutorial or a name for what it is I'm trying to do so I can research myself, it will be much appreciated. I'm stumped. I'm sure I'm just calling it the wrong thing and searching for the wrong terms in google.

Will I just need to create a macro, so that each consultant just runs that at the end of each month in order to add their new stats? Is that the simplest? It's looking the simplest now. Thanks.

Hi, I have a two workbooks (workbookA and workbookB). I am trying to call the macro workbookA's macro from workbookB. (The macro in the workbookA is actually doing the filtering workbookA data)

I am using below code in the workbookB to call the workbookA's macro

but when i run the macro below
Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim lastRow As Long
    Dim Rng As Range, TempRange As Range, aCell As Range
    
    On Error GoTo Whoa
    
    Application.ScreenUpdating = False
    
    '~~> Prepare textbox for output
    With TextBox1
        .Text = ""
        .MultiLine = True
        .WordWrap = True
        .ScrollBars = fmScrollBarsVertical
    End With
    
    '~~> Set the Sheet with which you want to work with
    Set ws = Sheets("Sheet1")
    
    
    With ws
        '~~> Remove autofilter if any
        .AutoFilterMode = False
        
        '~~> Get the Last Row
        lastRow = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
        
        '~~> Set your range
        Set Rng = .Range("A2:K" & lastRow)
        
        '~~> With the relevant range
        With Rng
            '~~> Set the autofilter
            .AutoFilter
            
            '~~> Filter data as per subject
            If CheckBox1.Value = True Then _
            .AutoFilter Field:=6, Criteria1:=CheckBox1.Caption
        
            If CheckBox2.Value = True Then _
            .AutoFilter Field:=7, Criteria1:=CheckBox2.Caption
            
            If CheckBox3.Value = True Then _
            .AutoFilter Field:=8, Criteria1:=CheckBox3.Caption
            
            If CheckBox4.Value = True Then _
            .AutoFilter Field:=9, Criteria1:=CheckBox4.Caption
            
            If CheckBox5.Value = True Then _
            .AutoFilter Field:=10, Criteria1:=CheckBox5.Caption
            
            If CheckBox6.Value = True Then _
            .AutoFilter Field:=11, Criteria1:=CheckBox6.Caption
            
            '~~> Get the resulting range
            Set TempRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
            
        End With
        .AutoFilterMode = False
    End With
    
    '~~> loop through resulting range and Add values from Name column to Textbox
    For Each aCell In TempRange
        If aCell.Column = 2 Then
            If Len(Trim(TextBox1.Text)) = 0 Then
                TextBox1.Text = aCell.Value & " - " & aCell.Offset(, 1).Value
            Else
                TextBox1.Text = TextBox1.Text & vbCrLf & aCell.Value & " - " & aCell.Offset(,
1).Value
            End If
        End If
    Next

LetsContinue:
    Application.ScreenUpdating = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub
i got below ERROR message

"Object Variable or With block variable not set"

How should i fix the this error? Any advice would be appreciated.

Hi,

I tried creating a macro in excel2007 workbook.I can see and access the macro in different excelsheets of the same workbook but when I am trying to access it from another work book I am unable to see it. Please let me know how to access a macro from one workbook that is created in another workbook.

Thanks

djkahc

Hi All,

I have two workbooks book1 and book2.
I have a macro named "Test" in book1.
How do i run this macro from book2

how to run a macro upon opening of a workbook?

Hi,

This should be very simple, but I am not able to find the solution.
I want to be able to run a macro stored in the same workbook when I click on a hyperlink present in one of the cells.

I do not want to create a control button to call the macro. Is it possible to give the destination of a hyperlink as a macro?

Thanks for your help!!


No luck finding an answer? You could always try Google.