Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
What are you trying to evaluate, as written Back2Top is simply a string and
nothing else. Regards, Peter T "Carim" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
On Jan 7, 6:28*pm, "Peter T" <peter_t@discussions wrote:
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" wrote in message ... 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- Hide quoted text - - 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Evaluate
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Evaluate question | Excel Programming | |||
evaluate function only in Excel application | Excel Programming | |||
Interpretation of Application.Caller.Parent.Evaluate | Excel Discussion (Misc queries) | |||
Alternatives to Application.evaluate(Long Formula) | Excel Programming | |||
Error 2015 with Application.Evaluate | Excel Programming |