CODEWITHSUNDEEP
excelcheckboxvba
user2703472
user2703472

Reputation: 45

Make only one checkbox tickable in a row?

I have 5 to 6 check boxes (active x) in a row and I have 50 rows.

Is there a way to make only one checkbox tick-able in a row ( only one answer either 1,2,3,4 or 5)?.

Any simple vba to do this , I don't want to write a code to every checkbox.

Upvotes: 0

Views: 9227

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Yes, it can be done BUT Why use ActiveX Controls and so much extra coding? Why not data validation list? See this screenshot

enter image description here

If you still want a VBA Solution then I would recommend using FORM Controls and use their ALT Text to configure it. I infact have in one of the SO posts shown how to use group similar controls using Alt Text

enter image description here

EDIT:

If you want to go down the VBA road then here is another alternative, which DOESN'T use any FORM/Active X controls

Arrange the sheet as shown in the image below.

enter image description here

Now paste this code in the Sheet Code Area

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Columns(2)) Is Nothing Then ClearCells Target.Row, 2
    If Not Intersect(Target, Columns(4)) Is Nothing Then ClearCells Target.Row, 4
    If Not Intersect(Target, Columns(6)) Is Nothing Then ClearCells Target.Row, 6
    If Not Intersect(Target, Columns(8)) Is Nothing Then ClearCells Target.Row, 8
    If Not Intersect(Target, Columns(10)) Is Nothing Then ClearCells Target.Row, 10
End Sub

Sub ClearCells(r As Long, c As Long)
    For i = 2 To 10 Step 2
        If i <> c Then
            With Cells(r, i)
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                .ClearContents
            End With
        End If
    Next i
    With Cells(r, c)
        With .Borders(xlDiagonalDown)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlDiagonalUp)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With
End Sub

Now all the user has to do is select any of the grey cell and it will be Crossed Out. Also if there is any other cross in the same row then it will be removed.

enter image description here

SAMPLE FILE

Upvotes: 1

Related Questions