How can I get the Caption of a Checkbox ? 1

[tbl]

I have a program with a lot of Control boxes and I need to be able to get their Name, Linked Cell and Caption. The first 2 were easy, but Caption does not seem to work in the same way. Can anyone help please? Is there any way of just referring to checkboxes and not combo boxes for example ?

Sub CycleThroughCheckboxes()
Dim CB As Object
For Each DrawObj In ActiveSheet.DrawingObjects
CBName = CB.Name 'OK
CBLinkedCell = CB.LinkedCell 'OK
CBLinkedCell = CB.Caption ' Does not work
Call DoSomething(CBName, CBLinkedCell, CBLinkedCell)
Next CB
End Sub

 

[keysol]

I beleive you could use the tag property and give it the same value as caption and refer to it instead of caption.

 

[tbl]

Thanks for the reply keysol,

I don't have any idea how to use the Tag property or even what it is. Can you give me any more info please ?
I have corrected my code in the meantime !

Sub CycleThroughCheckboxes()
Dim CB As Object
For Each CB In ActiveSheet.DrawingObjects
CBName = CB.Name 'OK
CBLinkedCell = CB.LinkedCell 'OK
CBCaption = CB.Tag ' Does not work
Call DoSomething(CBName, CBLinkedCell, CBCaption)
Next CB
End Sub

 

[MichaelRed]

I generally use a naming convention, which are often included in third party manuals. By consistiently using the sme convention and relating the names to the controltype and bound field (if it is a bound control) or your 'intent' (if not a bound cpontrol, the name of the label associated w/ the control is easily derived from the control name.

A BRIEF example might use three letters as the control type (for your example: chk --> CheckBox & lbl --> label) while the renmainder might refer to the control 'puppose', as in "Retal", "Wholsale", etc). So a check box could be chkRetail and it's associtated label lblRetail.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[tbl]

Thanks for the reply but this is too complicated for me - I'm only an engineer. I just want to get the text on the checkbox as I loop through the various checkboxes.

Richard

 

[baixinho]

i use CB.Text and works just fine for me

 

[tbl]

Thanks baixinho,
I have tried that but it doesn't work with Checkbox VBA Control Toolbar version which is what I am using. I had not realised that there would be a difference between addressing the Forms version and the Control Toolbar version.

Richard

 

[RobBroekhuis]

I'm not as familiar with the drawingobjects collection, but if you use the OLEObjects collection, then you can use something like

for each cb in activeworksheet.oleobjects
caption=cb.object.caption
next cb


Rob

 

[RobBroekhuis]

I just confirmed that the same thing works for DrawingObjects. Good luck.

Rob

 

[tbl]

Thanks Rob'

I have tried what you suggested, but without success.
this is my code.

Sub CycleThroughCheckboxes()
Dim cb As Object
For Each cb In activeworksheet.OLEObjects
Caption = cb.Object.Caption
Next cb
End Sub

I get "Object Required" error. If I replace "activeworksheet" with activesheet, then I get "ActiveSheet", but I also get an "Object does not support this method"
I'm working in Excel 2000, any ideas ?

Richard

 

[RobBroekhuis]

The following code works for me:

Dim cb As OLEObject
For Each cb In ActiveSheet.OLEObjects
Debug.Print cb.Object.Caption
Next cb

I'm also using XL2000. The checkboxes were put on the worksheet using the Control Toolbox. Where do you get the error? In the immediate window, what happens if you enter:
?activesheet.oleobjects.count



Rob

 

[tbl]

This is getting stranger and stranger Rob !

Immediate code gives the correct result of 4 items

Sub Rob()
Dim cb As OLEObject
For Each cb In ActiveSheet.OLEObjects
Debug.Print cb.Object.Caption 'error occurs here - object doesn't support this method....
Next cb

End Sub

 

[RobBroekhuis]

Again in immediate window, what happens when you say:

set ctrl=activesheet.oleobjects(1)
?typename(ctrl)
?typename(ctrl.object)

(for me, the answers are "OLEObject" and "Checkbox&quot


Rob

 

[tbl]

Good diagnosis Rob,
the problem is that I have also got Combo Boxes and other controls from the Forms toolbox on the sheet and they interfere. Your code works if I only have OLE checkboxes on the sheet. Everything works if I delete all Form toolbox types.
Absolutely brilliant, you have earned your star. Thanks for staying with it.

Richard

 

[acron]

Just for the record the following should work with Form objects,

    Sub CycleThroughCheckboxes()
    Dim CB As Object
        For Each CB In ActiveSheet.DrawingObjects
        CBName = CB.Name 'OK
        CBLinkedCell = CB.LinkedCell 'OK
        CBCaption = CB.Characters.Text
        Call DoSomething(CBName, CBLinkedCell, CBCaption)
        Next CB
    End Sub

A.C.

 

[Voga]

tbl asked: ...Is there any way of just referring to checkboxes and not combo boxes for example?...

Well, it would be easier if you named your controls like "chk...", "lbl...", "txt...", "cmd..." and so on in this manner. Then:

Dim MyControl As Controls

For Each MyControl In Controls
'referring to checkboxes only
If Left(MyControl.Name, 3) = "chk" Then
Call WhateverYouWantToDo 'Do your events
End If
Next
---------------------------------------------------------
You can also make to ignore comboboxes:

Dim MyControl As Controls

For Each MyControl In Controls
'ignoring comboboxes
If Left(MyControl.Name, 3) = "cmb" Then
Else
If Left(MyControl.Name, 3) = "chk" Then 'do events with checkboxes
Call WhateverYouWantToDo 'Do your events
End If
End If
Next
-----------------------------------------------------------
I know it's simple, but has always been working for me
Good luck

 

[Voga]

One more tip working with "Caption" property
Try this. Ensert this part of the code at the end of your Sub:

Dim MyControl As Controls

For Each MyControl In Controls
'referring to checkboxes only
If Left(MyControl.Name, 3) = "chk" Then
MyControl.Caption = "Got It!!!!"
End If
Next