How to access the full set of attributes for controls in a group?

[McAldo]

Does anybody know how to access the full set of attributes of a control when grouped?

I am trying to access the properties of a grouped set of checkboxes (for grouped I mean selected and right click - > Group)

It appears that grouped controls are not accessible through OLEObjects() but they can be accessed as shapes. For example, the following code will set all elements in the group Advertisement2 to .Visibility = False:

For Each T In ActiveSheet.Shapes("Advertisement2").GroupItems
    T.Visible = True
Next

However, most other properties, such as .Enabled or .Value are not accessible (probably because the controls are considered shapes).
I have tried all sort of solutions, including using
.groupItems("ControlName").Value
or
.groupItems("ControlName").Object.Value

But to no avail..

Does anybody have a clue about this and hopefully knows a workaround?


Thanks!

Al

 

[Norie]

How did you originally create these controls?

That could have a lot of bearing on things.

eg accessing the properties of controls from the Forms toolbar can be tricky

Also why do you have multiple controls?

Have you considered some other sort of control like a listbox, or even Data>Validation...?

 

[McAldo]

Hi, Thanks for the reply.

I have created all the controls manually as activex controls.

I use separated checkboxes rather than a listbox because the description that goes with the options is a bit long and it might be clearer for the final users what they are clcking on if I keep the options separated as checkboxes.

I would avoid the grouping thingy but it comes handy for switching visibility for a whole group of options (kind of having a multipage directly on the sheet rather than using a form).

Mmm, I really don't understand while grouping prevents from accessing controls witht he usual OLEObjects instructions..

 

[Norie]

How did you create these controls?

Which toolbar/menu did you use?

Are you sure a listbox isn't an idea?

How about a userform?

 

[McAldo]

The controls are created from Developer -> Insert -. ActiveX controls
Then they are grouped by selecting them and right click + Group

Listbox might be an option but I will have to find a way to add an explanation underneath, which is not complicated but might not be too neat.
The thing is supposed to be as intuitive as possible to compile.

I have already developed most of the other components to the application and I was trying not to have to redesign the thing, but probably you are right in suggesting using a userform...
I am just a bit worried to plunge into a whole new jungle of commands to understand, I guess

Thanks!

 

[Norie]

There honestly aren't a whole new 'jungle' of commands when using userforms.

It might actually be easier to use a userform.

What I was thinking was a listbox where the user choses an option and a label/textbox where the associated text can be displayed.

I can think of quite a few advantages of that approach, the first being that you would only be dealing with one control for the options.

Also you could allow users to make multiple selections and you could actually display option buttons/checkboxes next to each item if you wanted.

 

[McAldo]

Also you could allow users to make multiple selections and you could actually display option buttons/checkboxes next to each item if you wanted.

Is that possible?
I didn't know about it (well, I know very little about VBA anyway )
Are we talking about a combobox or a list box (sorry, nooby questions)

 

[McAldo]

I was thinking of another solution which migth not imply major changes to what I already have in place.
By any chance, do you know how to check whether a string contains a certain substring?

All checkboxes in each group have similar names, so I could just ungroup them and use the fact that all controls fro a group have something like GROUP1 in the name as a condition for switching visibility on/off.

Something like:

For Each T In Worksheets("Form").OLEObjects

if T.Object.Name  [Contains GROUP1 ] Then T.Object.Visibility = False

Next

Note sure how to code the [Contains GROUP1 ] bit.. Any hint?

 

[Norie]

There are ways to search strings for substrings - InStr, Like etc.

But I really think you should consider the userform and I did mean a listbox not a combobox.

Comboboxes have their uses but from what you've posted so far it sounds like a listbox would be more appropriate.

Then again I don't actually know what you are doing or the data you are dealing with.

 

[McAldo]

Uhh, well, the bloody thing is kind of a little decision support system.
I have a set of rules in rows, each of which with boolean conditions setting whether they can apply along with other rules and a subcode.

Basically, dependently on the choices of the user (each check box corresponds to a row), a code is constructed from the selected rules subcodes.

Some set of options exclude others: i.e. if you stay home you can play with your cat and/or program VBA, if you go out you can go to the cinema and/or to the restaurant, therefore you cannot really go to the restaurant AND program VBA, unless you are super geeky, and therefore the two sets GOING OUT and STAYING HOME are mutually exclusive.

Therefore I have my controls groups appear and disappear happily dependently on what the user choose (if he chooses going out I am not showing pet stroking and VBA mindblowing options, just rest and cinema)

Ok, the app is about planning, not restaurants, but that's the concept

Thank you so much for the advice, I will come back to this when I am a bit fresher, it was a long day

Have a nice weekend!

Al