Need checkboxes to appear or disappear based on values of certain cells

[djricketts]

I've tried variations of other solutions found here and elsewhere, but I can't make them work; looking for help.

Say I've got B1 thru B30 which will either be a number or a "" depending on the outcome of formulas therein. I need CheckBox1 thru 30 to exist only when isnumber(corresponding cell) is true and be invisible when false. Alternately, I could just make a helper field A1 thru 30 which will be, whatever... 1 when isnumber(colB) and 2 when not, and then use those values to switch visibility. Prefer these to be form checkboxes if possible, but whatever works. I do not want to hide any rows, just the checkboxes. Hope I'm making sense, I've got a bad case of tunnel vision

Thanks in advance!

 

[Damon Ostrander]

Hi djricketts,

To provide a solution it matters a great deal whether the values in column B are calculated or manually entered. In order to trap when a change occurs to a cell in column B one can use the worksheet's Change event directly if the column B values are manually entered, but otherwise must use the change event to monitor the cells the formula in column B is dependent on. Are the data in column B a mix of formula results and manually entered data? Or are they perhaps all formulas dependent on the corresponding row values in column A (or some other column)? I can give you a solution either way, but need to know the specifics.

Damon

 

[djricketts]

Col B cells have formulas and will be continuously/repeatedly calculated. And will be locked to prevent input. The user will input somewhere else (in a single cell) the number of B cells which will be "active" in the sheet's overall logic, and those cells will have number values while the rest will be blank, ie "". Although they can be zero instead of "" if that'd be easier, but I'd rather not have to hide zero values - I prefer to see zeros elsewhere on the sheet.

If it matters, the formulas in B9:B38 run like (in cell B38):

=IF(AND($D$2=TRUE,COUNT(B$8:B37)<$D$3),$D$3-COUNT(B$8:B37),IF(AND($D$2=FALSE,CELL("row",B38)-CELL("row",B$8)<=$D$3),CELL("row",B38)-CELL("row",B$8),""))

which is a way (undoubtedly clunky) of having it number 1 thru (D3) from top to bottom or bottom to top, depending on the state of D2. D2 & D3 will be the only inputs the user can make to change Col B. HTH!