How to detect the active control - Help...

09-04-2003, 04:45 PM

reijndop

Newcomer

Join Date: Sep 2003

Location: Dordrecht, Netherlands

Posts: 2

How to detect the active control - Help...

Hello. I am a not yet so very experienced developer in VBA. In an Excel sheet I have placed lots of checkboxes. When clicking them they all start, via the click event, the same macro. This macro should 'know' which checkbox activated it. I tried using "Var = ActiveControl.Name", but that results in error 424: Object required. What to do?

09-04-2003, 05:39 PM

Mike Rosenblum Mike Rosenblum is offline

Microsoft Excel MVP

Forum Leader
* Guru *

Join Date: Jul 2003

Location: New York, NY, USA

Posts: 7,848

I guess that you'd be looking for something along these lines:

Code:

Private Sub MyMacro(WhichCheckbox As Object)
    MsgBox WhichCheckbox.Caption
End Sub

Private Sub CheckBox1_Click()
    Call MyMacro(Me.CheckBox1)
End Sub

Private Sub CheckBox2_Click()
    Call MyMacro(Me.CheckBox2)
End Sub

Note that in the above that I had trouble declaring WhichCheckbox "As CheckBox". However, using "As Object" worked fine.

Hope this gets you going?

-- Mike

09-05-2003, 02:33 AM

mark007

Licensed to post

* Expert *

Join Date: Jul 2003

Location: York, Uk

Posts: 1,637

Mike's suggestion is good but you may also want to look at:

application.caller

09-05-2003, 08:25 AM

Mike Rosenblum Mike Rosenblum is offline

Microsoft Excel MVP

Forum Leader
* Guru *

Join Date: Jul 2003

Location: New York, NY, USA

Posts: 7,848

Hey Mark,

I don't think that Application.Caller will be very useful here. If the caller is a User-Defined Function, then .Caller will return the Cell of the caller. (Very nice.) Otherwise, it is not too useful, and in the case of Controls, will simply return #Ref! (According to the documented Help, I've not tried it.)

If the caller is a CommandBarControl, then CommandBars.ActionControl is a really nice function to tell you who did the calling.

But for controls placed on the Worksheet, I think the _Click() Event itself is the best way (only way?) to differentiate.

It's the only thing that I can think of...

-- Mike

09-05-2003, 09:05 AM

mark007

Licensed to post

* Expert *

Join Date: Jul 2003

Location: York, Uk

Posts: 1,637

Mike,

I disagree

, application.caller is very useful with controls. However I think it depends what controls we're talking about here, a checkbox off the forms toolbar or from the control toolbox on the VB toolbar?

I think something like this will replicate your code nicely with a forms toolbar checkbox:

Code:

Sub MyMacro()
    MsgBox sheets("Sheet1").checkboxes(application.caller).Caption
End Sub

The checkboxes collection is a hidden object that is undocumented. Right click on the libraries dropdown in the object browser and select show hidden members to reveal it.

In this case it appears that the control toolbox controls are being used so your suggestion may be the only one here, I'll have to have a play!

09-05-2003, 09:40 AM

Mike Rosenblum Mike Rosenblum is offline

Microsoft Excel MVP

Forum Leader
* Guru *

Join Date: Jul 2003

Location: New York, NY, USA

Posts: 7,848

Hey Mark,

I can't get yours to work.

And the documentation in "Help" suggests that Application.Caller should return #Ref! in this case. So I'm not sure that it should?

To be clear, I tried this:

Code:

Sub MyMacro()
    MsgBox Sheets("Sheet1").CheckBoxes(Application.Caller).Caption
End Sub

Private Sub CheckBox1_Click()
    Call MyMacro
End Sub

Private Sub CheckBox2_Click()
    Call MyMacro
End Sub

but got a Run-Time Error.

Admittedly, my solution is not great if you have lots and lots of identical buttions. If more than a couple of dosen buttons, I think one would have to move to a more generic event-trapping mechanism.

A good start for this would be at J-Walk: http://j-walk.com/ss/excel/tips/tip44.htm

-- Mike

09-05-2003, 09:45 AM

Timbo

Green-Eyed

Super Moderator
* Guru *

Join Date: May 2001

Location: Bangkok, Thailand

Posts: 10,261

I spent a while looking at that solution today but had no luck with it - kept getting "Object does not support automation events" for the Shape object which is (of course) the collection for controls on a worksheet.

Maybe you could do it if you referred to the checkboxes individually...

__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional

09-05-2003, 10:19 AM

mark007

Licensed to post

* Expert *

Join Date: Jul 2003

Location: York, Uk

Posts: 1,637

Hi Mike,

Like I said it depends on the type of checkbox you are using. If you add a checkbox from the forms toolbar then rather than having a click event you simply assign a macro. Therefore you can easily have many checkboxes callign the same macro. The checkboxes you are using are from the control toolbox toolbar and are activex controls that have events associated with them. For these application.caller does not work.

So as I said your solution may well be the only way in this case but if the checkboxes were replaced with ones from the forms toolbar then application.caller would become useful.

Try it out!

P.S. I tried it myself before just to make sure and it works well.

09-05-2003, 10:33 AM

Mike Rosenblum Mike Rosenblum is offline

Microsoft Excel MVP

Forum Leader
* Guru *

Join Date: Jul 2003

Location: New York, NY, USA

Posts: 7,848

Freakin' magic. Good work. So we need to use the "other" Checkbox...

It's funny how much easier that these old-school controls can be, eh?

MSFT "Help" has it wrong here. They write that in this case, Application.Caller would return #Ref! error. But, in fact, it is returning a String with the name of the Checkbox.

Awesome,
Mike

#10

09-05-2003, 12:48 PM

reijndop

Newcomer

Join Date: Sep 2003

Location: Dordrecht, Netherlands

Posts: 2

Hello all of you! Thank you for your input. It was very instructive
reading your discussions. As I indeed have lots of checkboxes (60...80) on several sheets that all trigger the same macro I believe that the j-walk solution will be the best solution for my issue.
Still rather strange that 'ActiveControl.Name' does not work...
Thanks again!
Pim

#11

09-05-2003, 02:50 PM

mark007

Licensed to post

* Expert *

Join Date: Jul 2003

Location: York, Uk

Posts: 1,637

It certainly is Mike. When I put controls on worksheets I nearly always use the 'old school' controls for this reason. They're quick and easy!