How can I link two checkboxes to one cell so that IFF two checkboxes are false, the cell is changed

Question

My first question!

I have created a tool which allows the selection (via checkboxes) of services to be offered at a hospital and which redistributes a nominated bed total across the services selected. For example, 1000 beds are distributed among 34 services according to a demand projection and deselecting any services adjusts the distribution so that bed counts for services are changing with every click of a checkbox. I have reached a pretty good point with the tool and its macros so far - select all and deselect all button for the services and collapsing the lists and hiding the checkboxes when collapsed. Also there are two bed numbers for each service, for medical and surgical.

Besides a complete deselection (unselection?) of services, I have a '% to service' column where a user puts in a number between 0 and 100 depending on how much of the demand the hospital should serve. I have both these functions (checkboxes and %) working separately to affect the bed number columns. If the % value > zero, then the two checkboxes must be true. Conversely, only if both the checkboxes are false, the % value should be zero.

So, % is 0 to 100 and affects both columns per service. And, each column has a checkbox per service.

I'd like to know where I can put the if statements which would have to go both ways. I can make it so that the state of one checkbox will zero the percentage, but then this 0% will erase both medical and surgical bed values. Or, if I put 0%, the checkbox state can't change if it contains an if cells.value >0 command.

I have also gathered some code ideas to try checking 2 checkbox states and change cell value (checkbox 1 is on row 31 and there are 34 rows and 2 columns so that the checkbox35 is next to checkbox1):

Private Sub Worksheet_Calculate()

For i = 1 To 34
If Me.Objects("CheckBox" & i).Value = False And Me.Controls("CheckBox" & i + 34).Value     = False Then
Range("f&i+30").Value = 0
End If

Next i
End Sub

I hope there is enough detail. The easier option for me will be to have % columns for medical and surgical and do away with the checkboxes. This really is a presentation thing, since the function work properly separately, but users need to click the boxes twice and 'select all' doesn't work.

default view: https://i.stack.imgur.com/110TC.gif zero% and checkbox is true: https://i.stack.imgur.com/Ap8ur.gif

Thanks so much for any help, guidance and question tips!

Answer 1

The problem with having the code in "Calculate", is that you can't determine whether the user clicked on a checkbox or edited the cell contents.

To get rid of this proble, I would suggest that you have distinct code for each of the events:

• If user clicks on a checkbox, you can use the Checkbox##_Click event, and place the checking code there. (Your CheckBoxes need to be ActiveX and not Form controls)

• If user changes the value in the worksheet, use the "Worksheet_Calculate" to check the corresponding checkbox.

You can rely on the fact that Excel/VBA will always process the events in the same order.

Now, one sad thing about CheckBoxes : You can access them throug the OLEObjects collection of the Sheet (ActiveSheet.OLEOBjects("CheckBox1") but you can't assign a value. So looping and using indirection does not work.

For i = 1 to 34
    ActiveSheet.OLEOBjects("CheckBox" & i).Value = ...
Next i

gives a runtime error doing the assignment to Value.

So your code would look like this :

Option Explicit

Private Sub CheckBox1_Click()
    CheckChanges 1, CheckBox1, CheckBox35
End Sub

Private Sub CheckBox2_Click()
    CheckChanges 2, CheckBox2, CheckBox36
End Sub

'And so on ...

Private Sub CheckBox2_Click()
    CheckChanges 34, CheckBox34, CheckBox68
End Sub


Private Sub CheckChanges(ByVal nr As Long, box1 As MSForms.CheckBox, box2 As MSForms.CheckBox)

    If box1.Value = False And box2.Value = False Then
        Range("f" & nr + 30).Value = 0
    End If
End Sub

Private Sub Worksheet_Calculate()

    Call SetBoxes(Range("F31").Value, CheckBox1, CheckBox35)
    Call SetBoxes(Range("F32").Value, CheckBox2, CheckBox36)
    ' And so on ...
    Call SetBoxes(Range("F67").Value, CheckBox34, CheckBox68)

End Sub

Private Sub SetBoxes(ByVal cellValue As Boolean, box1 As MSForms.CheckBox, box2 As MSForms.CheckBox)
    box1.Value = cellValue
    box2.Value = cellValue
End Sub