Re: Select one checkbox to Deselect another

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.Work***
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)








"geniussamu" <geniussamu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:67FB4C7A-DCCC-4014-893B-12842A49BD23@xxxxxxxxxxxxxxxx
I have five checkboxes from C7 to G7. I want the flow like this if I select
any one checkbox among those five the other four box should become unchecked
automatically. Please help.

.

Quantcast