Get and pass name of clicked ActiveX check box
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...
Last edited by CoolBlue; 06-02-2014 at 06:25 PM.
|