![]() |
|
#7
|
||||
|
||||
|
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. |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
How to Pass Document to this Sub
|
ilcaa72 | Word VBA | 1 | 01-28-2014 03:04 PM |
Can pass .expression as a parameter?
|
tinfanide | PowerPoint | 4 | 12-28-2012 06:14 PM |
| How to pass parameters to a .msg file? | HereNow | Outlook | 0 | 11-05-2012 10:38 AM |
| Office 2010 Excel Full pass | raineraus | Excel | 1 | 09-19-2012 12:55 PM |
How do I Convert All Legacy Check Boxes to ActiveX?
|
GreenPolyps | Word | 1 | 12-20-2011 05:11 PM |