Here's one way. In VBA, go to the "Insert" menu, choose "Module", and paste
the following code in that module.
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Option Compare Text
Dim ObjCollection As Collection
Dim ChkCollection As Collection
Sub Auto_Open()
Dim WS As Excel.Worksheet
Dim CKBox As CCheck
Dim OleObj As Excel.OLEObject
Set ObjCollection = New Collection
Set ChkCollection = New Collection
Set WS = ThisWorkbook.Worksheets("Sheet1")
For Each OleObj In WS.OLEObjects
With OleObj
If TypeOf .Object Is MSForms.CheckBox Then
If Not Application.Intersect(WS.Range("C7:G7"), .TopLeftCell) Is
Nothing Then
Set CKBox = New CCheck
CKBox.AddCheckBox .Object
CKBox.SetCollection ChkCollection
ChkCollection.Add .Object
ObjCollection.Add CKBox
End If
End If
End With
Next OleObj
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Then, in VBA, go to the "Insert" menu and choose "Class Module". Press F4 to
bring up the "Properties" window and change the Name from "Class1" to
"CCheck". In that class module, paste the following code:
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Option Compare Text
Private WithEvents pChkBox As MSForms.CheckBox
Private pCheckBoxes As Collection
Private pIgnore As Boolean
Private Sub Class_Initialize()
Set pCheckBoxes = New Collection
End Sub
Friend Sub AddCheckBox(CHK As MSForms.CheckBox)
Set pChkBox = CHK
End Sub
Friend Sub SetCollection(C As Collection)
Set pCheckBoxes = C
End Sub
Private Sub pChkBox_Click()
Dim N As Long
If pChkBox.Value = 0 Then
Exit Sub ' unchecked. get out.
End If
If pIgnore = True Then
Exit Sub ' internal event. get out.
End If
On Error GoTo ErrH:
pIgnore = True
With pCheckBoxes
For N = 1 To .Count
If .Item(N).Caption <> pChkBox.Caption Then
.Item(N).Value = 0
End If
Next N
End With
ErrH:
pIgnore = False
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
Finally, run the Auto_Open procedure to initialize things. (This will run
automatically when you open the workbook later.) The code makes all of the
check box objects whose Top Left Cell is in the range C7:G7 on Sheet1
mutually exclusive. Checking one will uncheck all the others. Check boxes
that are not within C7:G7 are not affected by the code. You can have as many
checkboxes as you want, name those checkboxes anything you want and the code
will continue to work. Note, though, that if you add checkboxes or change
their names, you'll need to run the Auto_Open procedure again.
--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
