Excel 2003

The following works within my code:-
Call CommandButton10_Click
where CB10 is one of many on a Userform
and CommandButton10_Click is a subroutine "on" the Userform.

The problem comes when I try to make the CommandButton10 part a variable.
My code will produce a value for the variable "MyCommandButton" which might
be CommandButton15 for example.
Then I try to make the call as follows:-
Call MyCommandButton(&"_Click")
a compile error "Expected Sub, Function or Property" results.

Is this just my bad syntax, or am I trying to do something which VBA doesn't
like?
donwb

I don't know of a facility in VBA that will allow you to execute the click
event by code. But I am not the ripest banana in the bunch.

"donwb" wrote:

> Excel 2003
>
> The following works within my code:-
> Call CommandButton10_Click
> where CB10 is one of many on a Userform
> and CommandButton10_Click is a subroutine "on" the Userform.
>
> The problem comes when I try to make the CommandButton10 part a variable.
> My code will produce a value for the variable "MyCommandButton" which might
> be CommandButton15 for example.
> Then I try to make the call as follows:-
> Call MyCommandButton(&"_Click")
> a compile error "Expected Sub, Function or Property" results.
>
> Is this just my bad syntax, or am I trying to do something which VBA doesn't
> like?
> donwb
>
>
>

Try at

Application.Run MyCommandButton & "_Click"

Tim

"donwb" <(E-Mail Removed)> wrote in message
news:e0Ru%(E-Mail Removed)...
> Excel 2003
>
> The following works within my code:-
> Call CommandButton10_Click
> where CB10 is one of many on a Userform
> and CommandButton10_Click is a subroutine "on" the Userform.
>
> The problem comes when I try to make the CommandButton10 part a variable.
> My code will produce a value for the variable "MyCommandButton" which
> might be CommandButton15 for example.
> Then I try to make the call as follows:-
> Call MyCommandButton(&"_Click")
> a compile error "Expected Sub, Function or Property" results.
>
> Is this just my bad syntax, or am I trying to do something which VBA
> doesn't like?
> donwb
>

the easiest way to overcome these problems is to insert a module and place
the required code within a sub in the module like this

Sub CommandButton1_Click_Routine()
MsgBox "this sub called from command button1"
End Sub

then in the event code you call the sub in the module like this

Private Sub CommandButton1_Click()
Call CommandButton1_Click_Routine
End Sub


You can then call the sub in the module from any other event routine and you
don't have to try to call the actual event.


--
Regards,

OssieMac


"Tim Williams" wrote:

> Try at
>
> Application.Run MyCommandButton & "_Click"
>
> Tim
>
> "donwb" <(E-Mail Removed)> wrote in message
> news:e0Ru%(E-Mail Removed)...
> > Excel 2003
> >
> > The following works within my code:-
> > Call CommandButton10_Click
> > where CB10 is one of many on a Userform
> > and CommandButton10_Click is a subroutine "on" the Userform.
> >
> > The problem comes when I try to make the CommandButton10 part a variable.
> > My code will produce a value for the variable "MyCommandButton" which
> > might be CommandButton15 for example.
> > Then I try to make the call as follows:-
> > Call MyCommandButton(&"_Click")
> > a compile error "Expected Sub, Function or Property" results.
> >
> > Is this just my bad syntax, or am I trying to do something which VBA
> > doesn't like?
> > donwb
> >
>
>
>