Forum Discussion
Solved
Is there a way to make a Form Control Checkbox make an embedded checkbox =True
In Column B I have embedded checkboxes, while in column C, I have multiple Form Control Checkboxes in a cell. The idea is once all the Form Control Boxes are checked in a cell from column C, (lets say C2), the corresponding embedded checkbox in B2 will auto check itself, signifying all is completed in C2.
I am happy to have one of the Form Control boxes activate the embedded box. Meaning. If there are 5 boxes in C2, when the 5th box is clicked (last box in the series) the embedded box in B2 gets ticked as well. Or it could be arranged where all 5 Form Control boxes have to be clicked to auto-tick the embedded box. I would be grateful either way. Below, if it helps is a screenshot.
I will assume that the check boxes are named Check Box 1, Check Box 2, ..., Check Box 5.
Create the following macro in a standard module:
Sub CheckBox_Click() Dim i As Long Dim f As Boolean f = True For i = 1 To 5 f = f And (ActiveSheet.Shapes("Check Box " & i).ControlFormat.Value = 1) Next i Range("B2").Value = f End SubAssign the CheckBox_Click macro to each of the check boxes in C2.
4 Replies
You have already accepted a solution that requires VBA, but why not get rid of the form controls. If you want/may, use 6 embedded check boxes as shown in the picture below and add a simple formula that returns either TRUE or FALSE 'under' the checkbox in B2. The latter will be checked only when all the others are checked.
I already used VBA to put several timers in the worksheets so I figured, since I'm already there, I'd use Form Control checkboxes to reduce the amount of columns required for the task. I really didn't know how it would end up, just what was needed, so I pretty much have been creating as I go along.
But now that I seen so many options perhaps I will rebuild the project with embedded checkboxes once I've seen the end product.
Thank you for the advice!
cheers
I will assume that the check boxes are named Check Box 1, Check Box 2, ..., Check Box 5.
Create the following macro in a standard module:
Sub CheckBox_Click() Dim i As Long Dim f As Boolean f = True For i = 1 To 5 f = f And (ActiveSheet.Shapes("Check Box " & i).ControlFormat.Value = 1) Next i Range("B2").Value = f End SubAssign the CheckBox_Click macro to each of the check boxes in C2.
Amazing! This works like a charm.
Thank you very much
