Launch Click Event Using Application.Run

[cellist]

I want to launch the code that handles a
Click Event procedure defined in one
workbook from another workbook. The
Click Event handler is defined in
workbook "Budget Payee Names.xls" and is
named cbAddPayee_Click.

Private Sub cbAddPayee_Click()
MsgBox "entered cbAddPayee_Click"
End Sub

When I run the following code in the
"other" workbook I get error 1004.

Sub launchAddPayee()
Application.Run "'Budget Payee Names.xls'" & _
"!cbAddPayee_Click"
End Sub

Run-time error '1004':
The macro "Budget Payee Names.xls
'!cbAddPayee_Click' cannot be found.

I think I've ruled out the usual suspects -- syntax error, subroutine name
typo. I've also tried the code with and without the "Private" on the click
event sub. Does the target of application.run need to be in a module, as
opposed to code that resides within the "sheet"?

Any help will be appreciated,

Phil

 

[Dave Peterson]

You know the codename of that sheet that holds that button, right?

Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("Budget Payee Names.xls")
Application.Run "'" & OtherWkbk.Name & "'!sheet1.cbAddPayee_Click"

Replace sheet1 with the codename for worksheet that owns that commandbutton.

Another way if you know the sheetname, but not its codename:

Dim OtherWkbk As Workbook
Set OtherWkbk = Workbooks("Budget Payee Names.xls")
OtherWkbk.Worksheets("Sheetnamehere").cbAddPayee.Value = True

 

[Gary''s Student]

Don't put any real content in the event handler. Just have it call a Public
sub in a standard module:

Public Sub hello()
MsgBox ("Hello World")
End Sub

So you event handler would just have

Call hello

as its single statement.

If the macro resides in Counter.xls, then the other workbook would only need:

Sub Macro1()
Application.Run "Counter.xls!hello"
End Sub

 

[Tom Hutchins]

First, you need to supply the path to "Budget Payee Names.xls" along with the
filename.

If supplying the path also doesn't make it work, then I suspect the problem
is that cbAddPayee_Click() is a Private sub, and therefore accessible only to
other procedures in the module where it is declared. Create a PUblic sub in a
general VBA module and put the essential code from cbAddPayee_Click() in it.
You may have to tweak it for sheet and cell references since it isn't in the
module attached to a particular sheet any more. Then you can call it from the
cbAddPayee_Click event and also from the other workbook using Application.Run.

Hope this helps,

Hutch

 

[cellist]

Thanks to Gary's Student, Tom Hutchins, and Dave Peterson for your replies.

Tom's and Gary's Students solutions are essentially the same idea. I haven't
tried this yet, but I had already successfully done an application.run for a
public procedure defined in a module. What I had not thought of was the idea
of putting all the essential code in a public module, calling it from the
other workbook, and calling it from the click event procedure when the actual
command button is clicked.

BTW, I didn't need the full pathname for the application.run. "Budget Payee
Names.xls" and the workbook where the application.run is issued are both
loaded when the saved workspace is opened.

Dave's solution is one I would never have thought of. I didn't realize that

OtherWkbk.Worksheets("Sheetnamehere").cbAddPayee.Value = True has the effect
of causing the command buttons click event to trigger.

Thanks to all for your help.

Phil