Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #7  
Old 06-02-2014, 03:50 PM
CoolBlue's Avatar
CoolBlue CoolBlue is offline Get and pass name of clicked ActiveX check box Windows 7 64bit Get and pass name of clicked ActiveX check box Office 2013
Advanced Beginner
 
Join Date: Jun 2014
Location: Australia
Posts: 40
CoolBlue is on a distinguished road
Default 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.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Get and pass name of clicked ActiveX check box How to Pass Document to this Sub ilcaa72 Word VBA 1 01-28-2014 03:04 PM
Get and pass name of clicked ActiveX check box 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
Get and pass name of clicked ActiveX check box How do I Convert All Legacy Check Boxes to ActiveX? GreenPolyps Word 1 12-20-2011 05:11 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:08 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft