Click buttons in a random order (1 Viewer)

isladogs · Nov 11, 2018

Having a brain fade here....

I have 6 buttons cmd1 to cmd6 that I want to click once each in random order.
The standard code is Me("cmd" & I)
It works fine for properties e.g. Me("cmd" & I).Visible but fails for events e.g.
Me("cmd" & I).Click => error 438 - object doesn't support this property or method
I also tried Me("cmd" & I) & "_Click" without success - invalid use of property with Me highlighted
Omitting Me and everything else I've tried shows the code in RED
e.g. ("cmd" & I)_Click or "cmd" & I_Click etc

Does anyone know the correct syntax for this?
At the moment I'm using Select Case as a temporary fix but there has to be a better way

theDBguy · Nov 11, 2018

Just curious... Have you tried it this way?

Call “cmd” & i & “_Click”

Sent from phone...

isladogs · Nov 11, 2018

Yup. Its shown in RED

theDBguy · Nov 11, 2018

Okay, how about?

Call Eval(“cmd” & i & “_Click”)

Cronk · Nov 11, 2018

My call is that it can't be done. And I'm very happy to be proven wrong.

You can set a commandbutton control to any one of your 6 buttons. If you check, there is a property .OnClick, not an event. The property can only be set to the setting on the button eg [Event Procedure].

Try it

Code:

dim ctl as commandbutton
set ctl = me("cmd" & 4)
debug.print ctl.onclick

Incidentally IMO, SelectCase isn't that bad a "work around".

isladogs · Nov 11, 2018

Well I hadn't tried that!
Not shown in RED BUT highlights "cmd" with 'compile error - variable not defined'

Tried defining cmd as String, Object, Control etc. No joy. Same error

So I tried removing quotes around cmd.
Compile error now highlights "_Click"
Tried removing those quotes and as expected the whole thing is in RED again

:banghead:

isladogs · Nov 11, 2018

Cronk said:
My call is that it can't be done. And I'm very happy to be proven wrong.

You can set a commandbutton control to any one of your 6 buttons. If you check, there is a property .OnClick, not an event. The property can only be set to the setting on the button eg [Event Procedure].

Try it

Code:

dim ctl as commandbutton set ctl = me("cmd" & 4) debug.print ctl.onclick

Incidentally IMO, SelectCase isn't that bad a "work around".

Hi Cronk
Its beginning to look that way ....
I'd previously tried variations with OnClick as well

I put the 3 lines of code in the loop and it ran OK printing [Event Procedure] to the Immediate window 6 times

So of course I then tried to run ctl.OnClick & Ctl.Click & ctl_Click and got errors each time

And of course you are right - Select Case isn't that bad.
However, I thought the method would work just like it does for properties

theDBguy · Nov 11, 2018

Okay, one last try...

CallByName “cmd” & i & “_Click”

If it doesn’t work, try declaring each sub as Public.

Sent from phone...

Dreamweaver · Nov 11, 2018

Me.Controls("Picture" & I).Picture = ""

isladogs · Nov 11, 2018

theDBguy said:
Okay, one last try...

CallByName “cmd” & i & “_Click”

If it doesn’t work, try declaring each sub as Public.

Sent from phone...

Hi DBG
I'd never even heard of the CallByName function.

Reading the help file it says that

The CallByName function is used to get or set a property, or invoke a method at run time using a string name.

I wasn't convinced that 'invoke a method' would include an event procedure

Unfortunately I never got that far - it failed for the same reason as before.
Compile error - variable not defined with "cmd" highlighted

So I tried testing on an actual control name:
CallByName "cmd1_Click" => Compile error - type mismatch
CallByName cmd1_Click => Compile error - expected function or variable

In fact CallByName has 3 required arguments & one optional

CallByName(object, procname, calltype,[args()])

Part Description
object Required; Variant (Object). The name of the object on which the function will be executed.
procname Required; Variant (String). A string expression containing the name of a property or method of the object.
calltype Required; Constant. A constant of type vbCallType representing the type of procedure being called.
args() Optional: Variant (Array).

Next I tried
CallByName cmd1, OnClick, vbMethod => error 438 again
CallByName cmd1, "OnClick", vbMethod => error 438 again
CallByName "cmd1", "OnClick", VbMethod => type mismatch again

So whilst I'd like to find a use for this function, I can't find any way of getting it to work even on a named control

Many thanks DBG but, in the absence of any other inspiration, it looks like I'll stick with Select Case which does of course work

---------------------------------------------------------
@MickJav
Thanks for your suggestion but I'd already tested that notation before I posted the thread.
As already stated it works for properties but not for events

theDBguy · Nov 12, 2018

Hi Colin,

Sorry. I couldn't get in front of a computer today because of the Holiday. I was enjoying the festivities outdoors.

Did you try what I said about declaring your subs into Public subs? Thank you for reminding me about the required arguments. I was able to make it work using the following syntax:

Code:

CallByName Me, "cmd" & r & "_Click", VbMethod

Please see the attached demo.

Hope it helps...

The_Doc_Man · Nov 12, 2018

Good point, DBGuy. If it ain't public, you can't see from anywhere but the form where the buttons occur. By default, all wizard-built control event code is Private.

MajP · Nov 12, 2018

What are you trying to do because this makes no sense? The call by name is nonsense. A workaround for poor coding and the fact you do not have delegates in vba.

An event procedure is no different than any other procedure. The only difference is that it can listen for an event to be raised. You would call an event procedure the exact same way you call any procedure in a class module.

Forms("someForm").Command1_Click
As stated you would likely have to make it public since default is private.

The event properties define what the event handler is, Event Procedure, Function, or Macro. The correct syntax is

Code:

Forms("SomeForm").Command1.onclick = "[Event Procedure]"
or 
Forms("SomeForm").Command1.onclick = "=SomeFunction()"

("cmd" & I)_Click or "cmd" & I_Click etc

That makes no sense. If you had

Code:

Public Sub Procedure1()
End Sub

You would never think about doing
call "Procedure" & I

MajP · Nov 12, 2018

Further you cannot raise events of Access Objects. You can define and raise your own custom events. You can handle the click event, but you cannot raise the click event or any other event. In other words you cannot write

Code:

RaiseEvent Command1_Click

and expect to raise the click event.

isladogs · Nov 12, 2018

@DBGuy
Many thanks for providing working syntax for this rather 'odd' CallByName function.

I never got as far as testing using Public as all my previous attempts using any code had failed for other reasons

I can confirm that the code does indeed work perfectly.
That is providing each of the command button click events are Public.
For the benefit of anyone else, using Private Sub for the button click events results in error 2465 (even when the CallByName code is running from the same form as is the case here).
Of course, using 'standard code', you can call a command button click event which has Private Sub from within the same form

@Cronk
Looks like we were both proved wrong

@MajP
I explained back in post 1 why I wanted to do this.
The 6 buttons each run a different test & I wanted a more concise version of my existing code to run each test in a random order and make them as fair as possible.

If you read the whole thread you will see I had previously tried various alternatives which work for procedures but not for events.

Not sure why you have to be so judgemental about code written by others.
Perhaps instead you could offer your own concise alternative to do the same thing without using what I described as the rather 'odd' CallByName function

MajP · Nov 13, 2018

Sorry, did not mean to sound judgmental. What made no sense is your original question as written. As written it does not say what you intended. I had no idea that all you were asking was a way to call a procedure by its name. It read as something completely different. It might have been clear to you but as written it is not.

I want to click once each in random order.

That is a very strange way to say that you want to call different event procedures in random order. I could only interpret that to mean you wanted to actually try to raise the the event. Then to make it more confusing you say

but fails for events e.g. Me("cmd" & I).Click

Which again does not look like calling an event procedure, but an attempt to raise an event.

There is a big difference between an event, event handler, event procedure, event property, and raising an event. When people use them interchangeably it gets real confusing. As for better code your original select case is probably as good as any. You are not gaining anything by this cludgy call my name. Just because code is more concise does not mean it is more efficient, maintainable. and fast.

isladogs · Nov 13, 2018

@MajP
I agree totally that using Select Case is perfectly effective and in fact I've decided to continue using that code.

I'd never heard of the CallMyName function and I'm very grateful to the DBGuy for bringing it to my attention. However I'm concerned that in a year's time when I look at that code again, I won't have a clue what it means.

Sometimes a few lines of extra code can indeed make for greater clarity

Click buttons in a random order (1 Viewer)

isladogs

MVP / VIP

theDBguy

I’m here to help

isladogs

MVP / VIP

theDBguy

I’m here to help

Cronk

Registered User.

isladogs

MVP / VIP

isladogs

MVP / VIP

theDBguy

I’m here to help

Dreamweaver

Well-known member

isladogs

MVP / VIP

theDBguy

I’m here to help

Attachments

The_Doc_Man

Immoderate Moderator

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

isladogs

MVP / VIP

MajP

You've got your good things, and you've got mine.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread