Hi to all,

I‘d like to pass the name (or parts of it) of a clicked check box (ActiveX) to a sub which – according to the passed characters – executes different commands

With Forms check boxes I can use Application.Caller to find out which check box has just been clicked. I’m looking for an ActiveX equivalent.

Example:
- Private Sub cb_1a_Click should do nothing but pass the number of the question (“1”) and the choice (“a”) to the Sub DoWithBox(oNum as String, oChr as String)
- Sub DoWithBox then runs different commands depending on passed oNum and oChr

My problem: I do not know how to retrieve the clicked check box’ name – I mean it is part of the Private sub heading, so it is known, but I simply (?) find no way to pass it. Maybe I just don’t have enough experience with Excel VBA.

Thanks for your help
NP

I don't use forms much in Excel (I do in Access), so I may be all wet here. But if this were my problem, I think I'd try passing the checkbox object itself. Then:

If that means a

with a manually typed 'Call DoWithBox "CB_1a"' included, then that's exactly what I would not like to do. Passing the name in this way, I'd have to change each Private _Click sub if (for what ever reason) I have to rename my check boxes.

I'd like to use the same code for each check box, sth. like 'Call DoWithBox CheckBox.Name'

Any other idea
NP

Hi,

Check this to see it helps.
http://www.exceltrick.com/how_to/sel...ngle-checkbox/

Nope, that's just another way of trying to pass the name of the checkbox. I think this would work:

Then in DoWithBox something like I'm not saying that's the way I'd solve the original problem; but if I'd already decided to try to work based on the object name, then this is probably how I'd go about doing that.

Is there a way for a control in the form to refer to itself? I think "Me" refers to the whole form, but maybe some object named "ThisControl" or something? Might simplify coding.

I just reread your post, NobodysPerfect, and notice you have the same objection to this that occurred to me:Well, almost the same. Doing it your way or mine (either one) would keep us from having to change the call when a control name changed. But we'd still have to change the evaluation in DoWithBox.

With the knowledge I have now I can't be sure what the idea approach would be. The general principle would be to keep the parts that vary with the checkbox in the checkbox, and make DoWithBox act the same way for every caller...if possible, of course. For instance, if we wanted to generate a different message for each selection (probably not the case here), then each selection should pass the message rather than the checkbox name:But that's just a principle; depending on what's needed, it may not be practicable.

Hi, I think the thing you are looking for is what's called a Control Array in VB. You can get the same effect in VBA by creating a class module to contain the OLEObject (in your case a tick box) and respond to it's events.
In the class module you do a public, "with events" declaration of the control and add any service routines you need.
Because you used a "with events" declaration for the control, you can also include event handling routines for it in the Class Module.
In the Click event handler you can return the .Name property of the control using the name you gave it in the Class Module.
You can now create instances of this class and connect your controls to the control in the class.
You could do this by looping through the OLEObjects collections exposed in the Worksheet object in the Workbook open or sheet activate event handler. You can declare a collection object in the Worksheet Module of the sheet where the controls are, to store an arbitrary number if these objects and Add the instances of your custom class object, with the controls connected to them, to a collection.
Having done that, all of the click boxes you wrapped in your custom class will use the same event handling routine in said class.

For example...
Make Custom Class called clsOLEObjectEvents and in it,
Include this property...
Public WithEvents gOLEObject As OLEObject
Dim WithEvents gCheckBox as Control
In the class Initialize routine include
Set gChecBox = gOLEObject.Object
(you will need to add apropriate error handling or switching to check the Control Type)
And this event handler...
Private Sub gCheckBox_Click()
Pump out gCheckbox.Name
End Sub
And then make an instance in your spreadsheet module...
Dim mOLEObject as clsOLEObjectEvents
Dim colCheckBoxes as Collection
Loop through your OLEObjects, checking type as you go and for the CheckBox Types...
For each nextOLEObject
Set mOLEObject = New clsOLEObjectEvents
' Add some code here to filter the controls...
' Connect the control and chuck it in the collection
Set mOLEObject.gOLEObject = nextOLEObject
colCheckBoxes.Add mOLEObject
Next nextOLEObject

Thats the rough idea anyway...

Thanks for your answers. I think, the 'class & event solution' is best.

Meanwhile I found these two sites:
http://www.vb-fun.de/cgi-bin/loadfra.../tip0178.shtml
http://www.jkp-ads.com/Articles/ControlHandler00.asp

I use the code of no. 1, as I found this link first. It's written in German but keywords like 'class', module, etc.' are so obvious, you should not encounter any language problem.

The second link is in English and the author offers a bunch of really, really good tools (and explanations).

Using two classes (one for check boxes and another for option buttons) I do not need a single 'Button_Click' procedure anymore. Saves plenty of time ...

NP

Yes, that's the two methods to make a control array: using an Array or a Collection.
I lean towards the Collection method because the .Add method has an optional Key parameter where you can stick the .Name property (for example) of the OLEObject and access members of the collection by name. The nice thing about that is that you can name it in the spreadsheet without lifting the lid on the code, thus smoothly feeding into the VBA domain from the user domain.

Another point is that you can generalise the events Class. You can make it so that you only need one class for multiple control types. You do this by having one of each control type in the events Class module (all declared "WithEvents") and using TypeName and a Select Case statement for the Let and Get on the control property of the events Class. In the Terminate method you can just set all of the objects to Nothing proceeded by an On Error Resume Next statement to ignore the error you get setting a Nothing object to Nothing.

Great! I did not even consider one class and Select Case instead of creating another class (what I did). Why not

(Programmer's) Life Can Be So Easy ....

Cheers
NP

No worries...
You can get quite oopy with VBA, you can also create a class to wrap the collection. If you are working with activex controls on a spreadsheet for example, you can have a ws property in the events class for the worksheet and in the Property Let for it have some code to loop through the controls and load the collection. So by just having
myControlEvents.ws=activesheet
You can wrap all of the controls and load the collection.

Hey, CoolBlue,

Even more complex and more possibilities …

As you seem to be very experienced with (ActiveX) controls and VBA, you might be able to answer my question:
I’m working on an evaluation sheet with 238 check boxes an 136 option buttons. My original intention was to use Word, as it’s more a formatting than a calculation effort.
For check boxes I can use FormControls in Word, but–other than Excel–Word does not offer option buttons as FormControls. So I need 136 ActiveX option buttons. I don’t know exactly up to how many ActiveX Controls Word can manage without problems, but 136 of them make Word take more than 10 sec. to open. 10 seconds is (much) more than any of 'my users' would except. Consequently we decided to switch to Excel. But Excel is not designed for formatting longer text, so it’s a PITA.

Do you have any idea, how I could speed up the Word document? If not, I will stick to Excel, hoping the text will not change frequently.

Sorry for continuing this thread with a somehow OT question in Excel programming forum … no need to move it to the Word forum, the topic already exists (https://www.msofficeforums.com/word-...o-buttons.html).

NP

I haven't done much with word but I had a look at your thread and yep... I got nothing to offer in that.

I can think of a few ways to do the text, text boxes... Pictures... You could build a user interface to manage that I guess... But I would need to understand how the document is used, like, who would maintain the text and how often... What kind of "look" you need for the text and what level of formatting you require... You would need to provide a bit of background there mate. Maybe a sample spreadsheet showing the typical structure?
I think you should start a new thread though.

Oh and sorry for my Un-hip-ness but what us PITA?

Sorry ... Pain in the back

NP

Oh yeh... Sorry I'm a bit blur sometimes when I'm in VBA mode.