can vba "click" a button

[tim963]

How would you run “CommandButton1_Click” from VBA, I want the Marco to “Click” the button on a user form, I have tried Application.Run "CommandButton1_Click" but it will not work I get “Marco commandbutton1_Click can not be found. Thanks excel ‘97

 

[elgringo56]

What does clicking the button do for you? if it initiates a macro, you can run the macro from another macro by doing an application run macro name.

 

[Legacy 98055]

Just type:
CommandButton1_Click
If your are running calling this procedure from another module, you will need to specify the class module. If the button is in UserForm1 for example:
UserForm1.CommandButton1_Click
If in a worksheet:
Sheet1.CommandButton1_Click
or
Sheets("Sheet Name").CommandButton1_Click
ect...

Tom

 

[tim963]

Thanks, I will give it a try

 

[Mike_Excel2003]

Re: can vba "click" a button

9 years on...
Clicking CommandButton2 in worsheet1, which contains code:

Private Sub CommandButton2_Click()
CommandButton1_Click
End Sub
​

Worked! Indeed, clicking button 2 gave made command button 1 run. Both buttons were in the same worksheet.

But replacing
CommandButton1_Click with UserForm1.CommandButton1_Click
gave this error:
Compile error: Method or data member not found

The UserForm1 exists and CommandButton1 exist. CommandButton1 was created from the form toolbox. Attempts to change to Forms.UserForm1.CommandButton1_Click also fail.

Interstingly, with UserForm1.CommandButton1_Click code, the debugger highlights the first line of the subroutine of CommanButton2 in the worksheet. i.e. Private Sub CommandButton2_Click() gets highlighted!

Can anyone come up with a different way a worksheet button can 'click' a form based button?

Thanks.

 

[Mike_Excel2003]

Re: can vba "click" a button

After many hours and a sinking feeliing it would never work, I solved my own problem :D

Clicking a worksheet CommmandButton to force a Form based CommandButton to 'click' was achieved with VBA with these two lines inside the worksheet CommandButton's click event:

UserForm1.CommandButton2 = True
UserForm1.CommandButton2 = vbClick

There are references on the web saying you can just do this:

UserForm1.CommandButton2_Click, or
Call UserForm1.CommandButton2_Click

but they didn't work

 

[Jaafar Tribak]

Re: can vba "click" a button

After many hours and a sinking feeliing it would never work, I solved my own problem :D

Clicking a worksheet CommmandButton to force a Form based CommandButton to 'click' was achieved with VBA with these two lines inside the worksheet CommandButton's click event:

UserForm1.CommandButton2 = True
UserForm1.CommandButton2 = vbClick

There are references on the web saying you can just do this:

UserForm1.CommandButton2_Click, or
Call UserForm1.CommandButton2_Click

but they didn't work

Just make the userform CommandButton2_Click Public such as :

[B][COLOR=Red]Public[/COLOR][/B] Sub CommandButton2_Click()
    'your code here.
End Sub

Then you can call it from anywhere like this :

UserForm1.CommandButton2_Click

 

[Mike_Excel2003]

Re: can vba "click" a button

Thanks Jaafar :D.

My previous reading when skilling myself up to do OOP, warned me against making things Public which could remain Private. As such, it built an impenetrable wall around my thought processes to devise your solution.

Thanks for the workaround. I'm sure it will open my mind up to other possible solutions in other instances in the future.

P.S. I could move to Excel 2007, but don't want to fork out the money (which would give me no real advantage - as far as I can tell) and I actually want to resist MS's money making schemes. They've done well enough in making Excel '03. My OS is > XP however

 

[Beavis_Ex]

Re: can vba "click" a button

After many hours and a sinking feeliing it would never work, I solved my own problem :D

Clicking a worksheet CommmandButton to force a Form based CommandButton to 'click' was achieved with VBA with these two lines inside the worksheet CommandButton's click event:

UserForm1.CommandButton2 = True
UserForm1.CommandButton2 = vbClick

There are references on the web saying you can just do this:

UserForm1.CommandButton2_Click, or
Call UserForm1.CommandButton2_Click

but they didn't work

Mike I just registered to say that you saved me !
Thank you man ! ... and the other solution don't work indeed ...

Shugs.

 

[Mike_Excel2003]

Re: can vba "click" a button

My pleasure.
I've had so many helpful people offer free assistance, that to post a solution was the least I could do.