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

my first question!

i have created tool allows selection (via checkboxes) of services offered @ hospital , redistributes nominated bed total across services selected. example, 1000 beds distributed among 34 services according demand projection , deselecting services adjusts distribution bed counts services changing every click of checkbox. have reached pretty point tool , macros far - select , deselect button services , collapsing lists , hiding checkboxes when collapsed. there 2 bed numbers each service, medical , surgical.

besides complete deselection (unselection?) of services, have '% service' column user puts in number between 0 , 100 depending on how of demand hospital should serve. have both these functions (checkboxes , %) working separately affect bed number columns. if % value > zero, 2 checkboxes must true. conversely, if both checkboxes false, % value should zero.

so, % 0 100 , affects both columns per service. and, each column has checkbox per service.

i'd know can put if statements have go both ways. can make state of 1 checkbox 0 percentage, 0% erase both medical , surgical bed values. or, if put 0%, checkbox state can't change if contains if cells.value >0 command.

i have gathered code ideas try checking 2 checkbox states , change cell value (checkbox 1 on row 31 , there 34 rows , 2 columns checkbox35 next checkbox1):

private sub worksheet_calculate()  = 1 34 if me.objects("checkbox" & i).value = false , me.controls("checkbox" & + 34).value     = false range("f&i+30").value = 0 end if  next end sub 

i hope there enough detail. easier option me have % columns medical , surgical , away checkboxes. presentation thing, since function work separately, users need click boxes twice , 'select all' doesn't work.

default view: http://i.imgur.com/nsxdykn.gif zero% , checkbox true: http://i.imgur.com/ek5mp15.gif

thanks help, guidance , question tips!

the problem having code in "calculate", can't determine whether user clicked on checkbox or edited cell contents.

to rid of proble, suggest have distinct code each of events:

• if user clicks on checkbox, can use checkbox##_click event, , place checking code there. (your checkboxes need activex , not form controls)

• if user changes value in worksheet, use "worksheet_calculate" check corresponding checkbox.

you can rely on fact excel/vba process events in same order.

now, 1 sad thing checkboxes : can access them throug oleobjects collection of sheet (activesheet.oleobjects("checkbox1") can't assign value. looping , using indirection not work.

for = 1 34     activesheet.oleobjects("checkbox" & i).value = ... next 

gives runtime error doing assignment value.

so code :

option explicit  private sub checkbox1_click()     checkchanges 1, checkbox1, checkbox35 end sub  private sub checkbox2_click()     checkchanges 2, checkbox2, checkbox36 end sub  'and on ...  private sub checkbox2_click()     checkchanges 34, checkbox34, checkbox68 end sub   private sub checkchanges(byval nr long, box1 msforms.checkbox, box2 msforms.checkbox)      if box1.value = false , box2.value = false         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)     ' , on ...     call setboxes(range("f67").value, checkbox34, checkbox68)  end sub  private sub setboxes(byval cellvalue boolean, box1 msforms.checkbox, box2 msforms.checkbox)     box1.value = cellvalue     box2.value = cellvalue end sub