I use different groups of controls in an excel sheet.
Each time a control in one group is clicked, I would like the controls
of the other groups to turn to "False".
I know how to code it control after control, but this is quite tedious!
Is ther any kind of code that could go "For all controls in group X,
when control is clicked, value of all controls in group Y and Z go to
False" ?
Thanks in advance,
Franck

What sort of controls,, ActiveX or forms, and what do you mean by turn to
False?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FB" wrote in message
oups.com...
I use different groups of controls in an excel sheet.
Each time a control in one group is clicked, I would like the controls
of the other groups to turn to "False".
I know how to code it control after control, but this is quite tedious!
Is ther any kind of code that could go "For all controls in group X,
when control is clicked, value of all controls in group Y and Z go to
False" ?
Thanks in advance,
Franck

These are ActiveX controls. They are linked to cells. When a control is
clicked, I would like the values of the cells linked to the controls in
the other groups to turn to 'False'. For now, I use stuff like

Private Sub CbxChina_Click()
If Range("AA7").FormulaR1C1 = "TRUE" Then
Range("AA5").FormulaR1C1 = "TRUE"
Range("AA4").FormulaR1C1 = "FALSE"
Range("AA6").FormulaR1C1 = "FALSE"
Range("AA15:AA22").FormulaR1C1 = "FALSE"
Range("AB4:AB29").FormulaR1C1 = "FALSE"
End If
End Sub

for individual controls, but as I have about 50 controls on my sheet, I
cannot do this systematically!

Franck

They're a bit inconsistent(!) in Office - in some apps you can talk about the
collection of all Controls while in others you have to be more specific
(TextBoxes, CheckBoxes, Buttons, etc). As far as I can see, Excel forms are
the latter but you can still save time by using a For...Each loop and
hopefully you haven't got too wide a selection of different types of control.
For example, the following will cycle around all checkboxes making them
False (you could use an If, perhaps with myChk.Name, to be more specific):

Dim myChk As CheckBox
For Each myChk In ActiveSheet.CheckBoxes
myChk.Value = False
Next myChk



"FB" wrote:

I use different groups of controls in an excel sheet.
Each time a control in one group is clicked, I would like the controls
of the other groups to turn to "False".
I know how to code it control after control, but this is quite tedious!
Is ther any kind of code that could go "For all controls in group X,
when control is clicked, value of all controls in group Y and Z go to
False" ?
Thanks in advance,
Franck

Thanks. What I would like to do is something like

Dim myChk As CheckBox
For Each myChk In <Group of Checboxes #1

Is it possible?

You can test a group like this

Dim ctl As Object
Dim i As Long

Application.EnableEvents = False
On Error Resume Next
For Each ctl In ActiveSheet.OLEObjects
If ctl.Object.GroupName = "xxx" Then
If Err.Number = 0 Then
Range(ctl.LinkedCell).Value = False
End If
End If
Next ctl

On Error GoTo 0
Application.EnableEvents = True

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"FB" wrote in message
ups.com...
These are ActiveX controls. They are linked to cells. When a control is
clicked, I would like the values of the cells linked to the controls in
the other groups to turn to 'False'. For now, I use stuff like

Private Sub CbxChina_Click()
If Range("AA7").FormulaR1C1 = "TRUE" Then
Range("AA5").FormulaR1C1 = "TRUE"
Range("AA4").FormulaR1C1 = "FALSE"
Range("AA6").FormulaR1C1 = "FALSE"
Range("AA15:AA22").FormulaR1C1 = "FALSE"
Range("AB4:AB29").FormulaR1C1 = "FALSE"
End If
End Sub

for individual controls, but as I have about 50 controls on my sheet, I
cannot do this systematically!

Franck

Looks fine. Thank you!

Only indirectly, i.e. if you've given the checkboxes names that reflect their
group. Then you could do something like:

If Left(myChk.Name,4) = "grp1" then myChk.Value = False

"FB" wrote:

Thanks. What I would like to do is something like

Dim myChk As CheckBox
For Each myChk In <Group of Checboxes #1

Is it possible?