vba excel checkboxes multiple cells

vba excel checkboxes multiple cells

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: http://i.imgur.com/NSXdYKn.gif zero% and checkbox is true:
http://i.imgur.com/Ek5Mp15.gif
Thanks so much for any help, guidance and question tips!