CheckBox group name

[Lista]

Hi,

I'm having troubles accessing this property (group name).

Private Sub BtnOK_Click()

Dim sh As Worksheet
Dim o As OLEObject
Dim cbx As CheckBox

Set sh = ActiveSheet

For Each o In sh.OLEObjects
    If TypeName(o.Object) = "CheckBox" Then
    MsgBox (o.Name)
    End If
Next

End Sub

This piece of code hands me their names in, but I'd like to limit the output to only one group of checkboxes.

Help, please.

Cheers,
Ivan.

[MSP77079]

Hi Ivan,

I am a little bit confused by the question and hoping to learn something from you. What do you mean by "group name"?

The way I think about this is: there are 2, very differnt types of checkboxes. The ones we can create using the Forms toolbar, and the ones we can create using the Controls Toolbox toolbar. The Forms toolbar also has an object called the "Group Box", which has a great feature that we can group option buttons without the need for VBA. When I think of "grouping" check boxes (or option buttons), I think of the "Group Box". But, I am not sure this is what you mean or not.

The checkboxes created using the Controls Toolbox are OLEobjects. The checkboxes created using the Forms toolbar are not; they are an older technology and are no longer documented (although they are still supported). Since the code you posted is giving you the names of the checkboxes, they must be OLEobjects.

I know of no way to "group" OLEobjects (but am eager to learn if you do). What I would do if I needed to treat OLEobjects as a group would be to give them Names that would allow me to distinguish one group from another. If that is not feasible, the next property you can use to distinguish one from another is the Caption. If that is not feasible, you can use the Top or Left property.

Hope this helps. If I am way off the mark with my answer, please let me know.

[Lista]

Hi,

I do understand what you mean.

And I do understand that there are differences between, let's say, check box, depending on how you create it: from a Forms toolbar or a Control Toolbox.

The later is more powerful, I reckon.

You say that there are no group names as such here, but if I look at the properties of a checkbox created from Control Toolbox, I do see such a field (GroupName). And this is the field I'm trying to access.

Thanks for your advice about alternative ways of grouping them. I've actually already started to work on 'group by names', and now I'm even more convinced that it's feasible and acceptable.

Cheers,
Ivan.

[MSP77079]

I had a feeling I was going to learn something from you.

I never noticed that property before. OK, I might be able to get you one step closer.

If you have tried this:

    Set cb = ActiveSheet.OLEObjects("CheckBox1")
    MsgBox cb.GroupName

You have noticed it does not work.

But this does:

    Set cb = ActiveSheet.OLEObjects("CheckBox1").Object
    MsgBox cb.GroupName

That is one of those funny things about OLEobjects embedded on worksheets. Sometimes you need to use .Object before being able to access a property, and other times it is not necessary. And I have no idea why and when it is necessary; I only know that it at first I do not succeed, try adding .Object.

If you get this to work in creating a grouping, let us know.

_____________________
Added later ...

Now that I have spent some time playing around with GroupName for OLEobject option buttons, it is exactly the ticket I was hoping it would be. You have no idea how difficult I was making life for myself simply because I was ignorant of this property.

Thanks again for posting the question!

[Lista]

Hi,

it works like a charm!

A guess we scrached each other's back, because me too was beginning to go into unnecessary coding to connect groups of check boxes, when this property makes it so painfully easy!

Cheers,
Ivan.