Macro code for clicking on Command Button in another workbook

[Raj]

Hi,

I have a workbook with macros. The macros open another workbook with
macros created by another person with code protected. A sheet in that
workbook has a command button that is to be clicked to run the macros
in that workbook. Is there a programmatic way by which I can click the
command button from my workbook macro code?

Thanks in Advance for the Help.

Regards,
Raj

 

[Ron de Bruin]

Hi Raj

See the help for Application.Run

 

[Will Anikouchine]

My problem is the same as Raj's. I wish to write code in VBA to, in effect, remotely click a command button on a different worksheet. I needd a bit more guidance than "see help for Application.Run". I don't even know where to find that.

Any help would be greatly appreciated.



Posted as a reply to:

Macro code for clicking on Command Button in another workbook

Hi

I have a workbook with macros. The macros open another workbook wit
macros created by another person with code protected. A sheet in tha
workbook has a command button that is to be clicked to run the macro
in that workbook. Is there a programmatic way by which I can click th
command button from my workbook macro code

Thanks in Advance for the Help

Regards
Raj

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[Chip Pearson]

A Sub procedure can be assigned to a shape or button, either
programmatically (via code) or by right-clicking the shape and
choosing "Assign Macro". If you know the name of the procedure
assigned to the shape or button, you can call that procedure directly,
by-passing the shape entirely. If you don't know the name of the
procedure, right-click the shape and choose "Assign Macro" and the
assigned macro name will be displayed. Once you know that, just use in
your code something like:

Sub ABC()
' your code
Application.Run "TheProcName"
' more code
End Sub

where "TheProcName" is the procedure assigned to your shape or button.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dave Peterson]

Saved from a previous post (but very similar to your question about clicking a
commandbutton on a different worksheet in the SAME workbook).

(The original poster used the names of Harry and George for his/her
commandbuttons. You'll want to modify them to match your names. The button
Harry "clicked" the button George.)

You have at least a couple of choices.

This is the code I used under the Sheet2 module.
Option Explicit
Sub George_Click()
MsgBox "hi from George"
End Sub

Notice that the Private keyword is gone. That's so the "Call" can find the
procedure.

This is the code under the Sheet1 module:
Option Explicit
Private Sub Harry_Click()
Call Sheet2.George_Click
End Sub

=========
Another way:
Under Sheet2:
Option Explicit
Private Sub George_Click()
MsgBox "hi from George"
End Sub

Notice that the Private keyword is back.

And under Sheet1:
Option Explicit
Private Sub Harry_Click()
Application.Run "Sheet2.George_Click"
End Sub

(in both of these cases, that Sheet2 is the code name for the sheet--not the
name you see in excel on the sheet tab.)

=======
And one more way:
Under Sheet2:
Option Explicit
Private Sub George_Click()
MsgBox "hi from George"
End Sub

Under Sheet1:
Option Explicit
Private Sub Harry_Click()
Worksheets("Sheet2").OLEObjects("George").Object.Value = True
End Sub

 

[Will Anikouchine]

Thank you for your response. I have not been able to apply your suggestions. I am using Excel 2003. When I bring up the excel workbook, go to the appropriate sheet and right click on the command button (with a caption: CONVERT) nothing happens. I am not able to perform the "assign macro" action.

I am able to use VB editor to explore the macros contained in the workbook, but I cannot ascertain what the "name of the procedure assigned to the button" represents. The code for the click option of the button calls several SUBs. One is called "convert_points". I have tried to use both CONVERT and Convert
_Points in the Application.Run code that you recommended but neither work.

What am I doing wrong?



Posted as a reply to:

Re: VBA code to send click command to a macro in a different Excel workbook
19-Sep-09

A Sub procedure can be assigned to a shape or button, either
programmatically (via code) or by right-clicking the shape and
choosing "Assign Macro". If you know the name of the procedure
assigned to the shape or button, you can call that procedure directly,
by-passing the shape entirely. If you don't know the name of the
procedure, right-click the shape and choose "Assign Macro" and the
assigned macro name will be displayed. Once you know that, just use in
your code something like:

Sub ABC()
' your code
Application.Run "TheProcName"
' more code
End Sub

where "TheProcName" is the procedure assigned to your shape or button.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 19 Sep 2009 17:03:35 -0700, Will Anikouchine wrote:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx

 

[Will Anikouchine]

Hello,

Thanks for your response.
How can I determine the "procedure assigned to my button"? Is this the name of the SUB called when the button is pressed manually or has Excel assigned a name for my button that is hidden?

Will A.



Chip Pearson wrote:

Re: VBA code to send click command to a macro in a different Excel workbook
19-Sep-09

A Sub procedure can be assigned to a shape or button, either
programmatically (via code) or by right-clicking the shape and
choosing "Assign Macro". If you know the name of the procedure
assigned to the shape or button, you can call that procedure directly,
by-passing the shape entirely. If you don't know the name of the
procedure, right-click the shape and choose "Assign Macro" and the
assigned macro name will be displayed. Once you know that, just use in
your code something like:

Sub ABC()
' your code
Application.Run "TheProcName"
' more code
End Sub

where "TheProcName" is the procedure assigned to your shape or button.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 19 Sep 2009 17:03:35 -0700, Will Anikouchine wrote:

EggHeadCafe - Software Developer Portal of Choice
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorial...b-7374d3da3425/wpf-and-the-model-view-vi.aspx

 

[JLGWhiz]

If you read Chip's response carefully, you will see that the name of the Sub
is the name displayed in the small window at the top of the dialog box for
macros after you have right clicked the button. Excel does not change the
names of macros. The button name and the macro name are two separate
entities. The button name applies only to the control itself and it might
have one name that Excel uses and one that you use in code that are
different, but the macro name will not be affected.