vba 在一行中只勾选一个复选框？

Make only one checkbox tickable in a row?

提问by user2703472

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

我有 5 到 6 个复选框（活动 x），我有 50 行。

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

有没有办法在一行中只勾选一个复选框（只有一个答案是 1、2、3、4 或 5）？。

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

任何简单的 vba 来做到这一点，我不想为每个复选框编写代码。

回答by Siddharth Rout

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

是的，它可以做到，但是为什么要使用 ActiveX 控件和这么多额外的编码？为什么不是数据验证列表？看这个截图

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

如果您仍然需要 VBA 解决方案，那么我建议使用 FORM Controls 并使用它们ALT Text来配置它。事实上，我在其中一篇 SO 帖子中展示了如何使用组类似控件使用Alt Text

EDIT:

编辑：

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

如果你想走 VBA 之路，那么这里是另一种选择，它DOESN'T使用任何 FORM/Active X 控件

Arrange the sheet as shown in the image below.

如下图所示排列工作表。

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.

现在用户所要做的就是选择任何灰色单元格，它将是Crossed Out。此外，如果同一行中有任何其他十字架，那么它将被删除。

SAMPLE FILE

样本文件