How can I call a macro from another workbook that is not in a module? VBA

Question

I am trying to execute a macro from another workbook and have been unable to do so, I think, because it must be in a module. However, I am trying to "click" a button on a particular worksheet and cannot transfer it to a module. Is what I'm trying to do even possible? If you need more info just say so.

Answer 1

It is possible.  I don't have time at the moment.  But will edit this later to give you a working example.

Answer 2

You'll need to move whatever code is contained in the click event for the button into a module. Then, call the module procedure from the button or from the external macro.

In Book1 I have a button with the following:

Private Sub CommandButton1_Click()

hello_macro 'calls the public procedure in the module

End Sub

In the same workbook, I added a module with the following procedure:

Public Sub hello_macro()

MsgBox "Hello World"

End Sub

Then, in a second workbook I added a button and added the following code to the button:

Private Sub CommandButton1_Click()

Application.Run ("Book1.xls!hello_macro")

End Sub

There are more elaborate examples around...

http://www.google.ca/search?hl=en&hs=jlv...