run macro in an other workbook

[cazdealer]

Hi,

Can I make run a macro that is written in an other workbook from another workbook.

for exemple,

in a module of main.xlsm, can I write something in that that would :

activate test.xlsm (assuming test.xlsm is already openned)
run macro Start1 (that is written in VBA of test.xlsm and not main.xlsm).

thanks

 

[ColinKJ]

Hi cazdealer,

One way of doing it would be.

Put this in the WB Test.xlsm

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(Target.Row, Target.Column) = 1 And Target.Row = 1 And Target.Column = 1 Then
Start1
Cells(1, 1) = ""
End If
End Sub

And this in your WB main.xlsm

Sub activateTest()
Workbooks("Test.xlms").Sheets("Sheet1").Cells(1, 1) = 1
End Sub

This assumes both WBs are open

When you run the Sub activateTest(), the Private Sub Worksheet_Change event in the Test WB picks up the acton and runs the Start1 macro.

 

[GTO]

Another way: In the calling wb:

Sub CallOtherWBMac()
    Application.Run "'New Microsoft Excel Worksheet.xls'!Module1.Test", ThisWorkbook.Name
End Sub

Sub Test(msg As String)
    MsgBox "I was called from " & msg
End Sub

I included one arg being passed just to show. The single quotes and string construction is always the PITA to me...

 

[cazdealer]

How do I modified this to make it work with my problem?
Can I wrote this below (modified) in my main.xlsm to start macro start1 from test1.xlsm?

Sub CallOtherWBMac()
    Application.Run "'New Microsoft Excel Worksheet.xls'!Module1.Test", ThisWorkbook.Name
End Sub

I don't understand the New Microsoft Excel Worksheet.xls part...

 

[GTO]

How do I modified this to make it work with my problem?
Can I wrote this below (modified) in my main.xlsm to start macro start1 from test1.xlsm?

Sub CallOtherWBMac()
    Application.Run "'New Microsoft Excel Worksheet.xls'!Module1.Test", ThisWorkbook.Name
End Sub

I don't understand the New Microsoft Excel Worksheet.xls part...

That was just the name of the new file I created to test. You would have something like: "test.xlsm!Module1.start1", where Module1 needs to be the name of the Standard Module that you have the procedure start1 housed in.