Application.Evaluate

[Carim]

Hi,

In a class module, within a Private Sub ButtonGroup_Click() , I cannot
have the
Application.Evaluate to execute itself ...
I am stuck with these two lines :

Back2Top = "CommandButton" & z & "_Click"
Application.Evaluate (Back2Top)

Thanks a lot for your help
and Happy New Year
Cheers

 

[Peter T]

What are you trying to evaluate, as written Back2Top is simply a string and
nothing else.

Regards,
Peter T

 

[Carim]

Not sure to understand your remark ...
The string is the instruction I would like to have executed ...
How should I use the Application.Evaluate instruction to execute
the Command#_Click() ... ?

Thanks for your help

 

[Peter T]

A string is a string, like "abc", it's not an instruction or a formula. I
suspect you don't mean evaluate. Also a 'Sub' does not return a result like
a function, so what you mentioned in your OP does not make any sense.

If(?) you mean you want to "call" the procedure named Command#_Click() that
exists in a sheet module, try something like this -

sProc = Worksheets("Sheet1").CodeName & ".CommandButton" & i & "_Click"
Application.Run sProc

or
sProc = "CommandButton" & i & "_Click"
CallByName Worksheets("Sheet 1"), sProc, VbMethod

Or if(?) the procedure is in a Userform and you are calling it in a userform
sProc = "CommandButton" & i & "_Click"
CallByName Me, sProc, VbMethod

You will need to change Private to Public before the procedure names in the
sheet/userform module (or simply delete Private).

Regards,
Peter T

 

[Carim]

A string is a string, like "abc", it's not an instruction or a formula. I
suspect you don't mean evaluate. Also a 'Sub' does not return a result like
a function, so what you mentioned in your OP does not make any sense.

If(?) you mean you want to "call" the procedure named Command#_Click() that
exists in a sheet module, try something like this -

sProc = Worksheets("Sheet1").CodeName & ".CommandButton" & i & "_Click"
Application.Run sProc

or
sProc = "CommandButton" & i & "_Click"
CallByName Worksheets("Sheet 1"), sProc, VbMethod

Or if(?) the procedure is in a Userform and you are calling it in a userform
sProc = "CommandButton" & i & "_Click"
CallByName Me, sProc, VbMethod

You will need to change Private to Public before the procedure names in the
sheet/userform module (or simply delete Private).

Regards,
Peter T







- Show quoted text -

Peter,

Thanks a lot ... You are absolutely right ...
And you have just opened my eyes ... !!!
I 'm gonna rush to this section to implement your recommendation...
Best Regards ... and Best Wishes for the new year ...
Carim

 

[Carim]

Peter,

As a matter of fact, the procedure : Private Sub ButtonGroup_Click()
is located in a class and I am calling it
from the very same userform ...
and in one instance, it has to call itself i.e. a CommandButton Click
will trigger automatically the next
CommandButton Click ...
Should I use your recommendation :
sProc = "CommandButton" & i & "_Click"
CallByName Me, sProc, VbMethod

Thanks again for your precious help
Cheers
Carim

 

[Peter T]

is located in a class and I am calling it

from the very same userform ...

I don't quite follow but if the code is not in the class/userform that
contains the procedures, change 'Me' to an object reference that refers to
the class, eg

CallByName cls, sProc, VbMethod
where 'cls' refers to the class that contains the proc's

However if you are calling in the same class or userform as the procuderes,
indeed use the 'Me' keyword to refer to the class/userform
CallByName Me, sProc, VbMethod

Regards,
Peter T

PS yes, use the CallByName method if you need to call procedures in class or
userform, App.Run method wouldn't work

 

[Carim]

Peter,

Again you are right ... there is only one procedure
named Public Sub ButtonGroup_Click()
located in the class ( Class1 )
and the last instruction :
CallByName Me, "CommandButton" & z & "_Click", VbMethod
triggers a run-time error 438 ...
....
Thanks again for your help
Carim

 

[Peter T]

Again I don't quite follow, is both "CommandButton" & z & "_Click" and the
CallByName code located in Class1. If so the code you posted should work,
assuming a Public procedure named
"CommandButton" & z & "_Click"
exists in the class

If you are trying to call ButtonGroup_Click, why not simply
Me.ButtonGroup_Click
or
cls.ButtonGroup_Click

See "CallByName" in help.

Regards,
Peter T

 

[Carim]

Peter,

May I step back a little bit to let you know briefly about the
context ...
A Userform with 20 CommandButtons, which are all managed by a single
procedure :
Public Sub ButtonGroup_Click()
This procedure is located in the single class : Class1
Everything works fine but the very last instruction for a given random
CommandButton which has to act as if the user had clicked himself on a
another CommandButton ...
Hence, this loop I am trying to achieve ...whereby the procedure calls
back itself ...
Hope my explanation is clear enough ...
Cheers
Carim

 

[Carim]

Peter,

I have already tried using
Me.ButtonGroup_Click...
I get a run-time error 28 (out of stack space)
and it does not pass the id or # to identify which CommandButton is to
be clicked ...
Cheers

Carim

 

[Carim]

Peter,

For the sake of reference purposes, I searched for
the programme structure ... I could not remenber it ...
and I just found it back :
How to Handle Multiple UserForm Buttons With One Subroutine
http://j-walk.com/ss/excel/tips/tip44.htm

Cheers
Carim

 

[Peter T]

If I follow, ButtonGroup_Click() is in Class1 and Command#_Click() is in a
Userform, seems like a strange setup.

When you create Class1 pass a reference of the form, eg

assuming you are creating the class from within the form

' in the form, say in the initialize event
Set c = New Class1
Set c.propFrm = Me
c.aa

' in class1
Private moFrm As Object ' top of module

Public Property Set propFrm(oFrm As Object)
Set moFrm = frm
End Property

Public Sub ButtonGroup_Click()

CallByName moFrm, "CommandButton" & z & "_Click", VbMethod
End Sub

If the reference that store the class is not maintained in the userform, the
class needs to be destroyed before unloading the form, or rather in
particular 'moFrm' needs to be released.

It's difficult to understand what you have overall, but I suspect you might
be better trapping all your button events in a WithEvents class. That means
you only need write the Click event once for all your buttons.

Regards,
Peter T

 

[Carim]

Peter,

Thanks again for your very kind assistance.
All your comments have indeed helped me to take a new perspective
at this problem ... I was totally stuck ... and over-complicating
things ...

Finally, going step by step, just found the simplest solution :
Set CmdBtn = UserForm1.Controls("CommandButton" & i)
CmdBtn.Value = True

Hope it will help future readers.
Again thanks for sharing all your thoughts
Best Regards
Carim