#1
|
|||
|
|||
Get and pass name of clicked ActiveX check box
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 |
#2
|
||||
|
||||
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:
Code:
Sub DoWithBox(cb) Select Case cb.Name Case "1a": 'do whatever Case "1b": 'do something else Case "2a": 'and so on End Select . . . |
#3
|
|||
|
|||
Quote:
Code:
Private Sub CB1a_Click Call DoWithBox "CB1a" Sub I'd like to use the same code for each check box, sth. like 'Call DoWithBox CheckBox.Name' Any other idea NP |
#4
|
|||
|
|||
|
#5
|
||||
|
||||
Quote:
Quote:
Code:
Private Sub CB1a_Click DoWithBox CB1a . . . Code:
Sub DoWithBox(cb) Select Case cb.Name Case "CB1a": 'do whatever Case "CB1b": 'do something else Case "CB2a": 'and so on End Select . . . 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. |
#6
|
||||
|
||||
I just reread your post, NobodysPerfect, and notice you have the same objection to this that occurred to me:
Quote:
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: Code:
Private Sub CB1a_Click DoWithBox "For question 1 you chose answer a." End Sub Sub DoWithBox(msg) MsgBox msg . . . End Sub |
#7
|
||||
|
||||
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. |
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
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. |
#10
|
|||
|
|||
Quote:
(Programmer's) Life Can Be So Easy .... Cheers NP |
#11
|
||||
|
||||
Get and pass name of clicked ActiveX check box
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. |
#12
|
|||
|
|||
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 |
#13
|
||||
|
||||
Get and pass name of clicked ActiveX check box
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? |
#14
|
|||
|
|||
Sorry ... Pain in the back
NP |
#15
|
||||
|
||||
Oh yeh... Sorry I'm a bit blur sometimes when I'm in VBA mode.
|
|
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 |