Need help to create checkbox limits per column

mantiscore

New Member
Joined
Sep 15, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,
I am working on a matrix.
I have checkboxes in all cells of the matrix, and based on that a calculation is made. However i need something to limit the filled checkboxes to one per column. Is there any way i can do it?
Thanks in advance, if more information is needed im happy to provide it quickly.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Are the checkboxes joined with cells? Then you could use COUNTIF function to alert user (for instance with conditional formatting) that There is more than one in given column.

The second possibility is to use VBA and in the code of a given sheet use Change event handler to analyze after any change of cell value in a workbook (or a range) if more than one value in a column is TRUE and if it is - display a message and UnDo last change.
 
Upvote 0
You could use Form Control checkboxes and use the .OnAction property
so they all call the same macro that could be along the lines of this
VBA Code:
Private Sub RunForMatrixChkBoxes()

    Dim col As Long

With ActiveSheet.CheckBoxes(Application.Caller)
    col = .TopLeftCell.Column
    If Application.WorksheetFunction.CountIf(Columns(col), True) > 1 Then
        MsgBox "This column already has a checkbox checked" & vbLf & _
               "Will uncheck that last one"
        .Value = False
    End If
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top