Grouping Checkboxes

[tracktor]

I have checkboxes on my worksheet that need to be grouped together. If there
are 2 or more checkboxes I only want 1 of them to be checked, and the
other(s) to be unchecked.. In other words if 1 checkbox value is true, I want
the others in the group to be false.

 

[Rick Rothstein \(MVP - VB\)]

Why are you using CheckBoxes instead of OptionButtons? OptionButtons have
the functionality you want built-in whereas you will have to create code to
duplicate the functionality if you use CheckBoxes. Next question... where
did you get your CheckBoxes (or OptionButtons if you decide to go with my
suggestion) from... the Forms toolbar or the Visual Basic toolbar?

Rick

 

[tracktor]

I began using checkboxes when I first started creating this workbook, only to
find out later that it was going to be a problem if the user selected more
then one checkbox. However, now I have well over 100 checkboxes on 1
worksheet that are linked to 100's of cells in other worksheets.

I got the checkboxes from the Forms Toolbar

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure grouping the CheckBoxes will control them in the same way that
grouping OptionButtons from the Forms toolbar does; but there is a GroupBox
object on the Forms toolbar that when placed around two or more
OptionButtons restricts the selections to among those grouped OptionButtons
no matter how many other OptionButtons are placed on the worksheet or within
other GroupBoxes. You can try that and see if it will do the same for your
CheckBoxes, but my thinking is it will not (as I said, you are using the
wrong tool for what you are wanting to do). The only other way to do what
you want is strictly through code (lot's of If-Then filtering statements I
presume).

Rick

 

[tracktor]

I have played around with the OptionButtons on the forms toolbar and they
work how I want them to but the group box is visible and in the way,
therefore unless I can hide the group box then I don’t want to use them, but
I was playing around with the OptionButtons on the control toolbox, and
apparently they need some code to make them work and group together. Can you
walk me through this?

 

[Rick Rothstein \(MVP - VB\)]

You can change the text on the Group Box to give it a more meaningful title.
Also, you can simply remove the text altogether, from the Group Box and just
leave a simple rectangle. As for the OptionButton from the Control
Toolbar... this is a more robust control that comes with events for reacting
to user interaction. Grouping is actually quite simple for them... the come
with a GroupName property. Just give the same name to the OptionButtons you
want linked together and different names to other groupings and each group
will act independent of each other with no code required to handle the
differentiation between them.

Rick

 

[Dave Peterson]

You can hide the groupboxes using a line of VBA code:

Select the worksheet with the optionbuttons (and groupboxes)
Hit alt-f11 to get to the VBE (where macros and UserForms live)
hit ctrl-g to see the immediate window
Type this and hit enter
activesheet.groupboxes.visible = false

When you want to see them again, do the same thing, but change that False to
True.
Then close the VBE window and go back to excel to see if it worked ok.

If the layout of the optionbuttons is "nice", you may want to look at this page
on Debra Dalgleish's site:
http://contextures.com/xlForm01.html

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm