Placing values in a cell using checkbox macro

[kwroche]

I have a checkbox macro that calls a macro when its on and calls a different macro when its off. The macro is as follows:

Sub OxygenCheckBox()
Dim cb As Excel.CheckBox


Set cb = Excel.ActiveSheet.CheckBoxes(Application.Caller)
If cb.Value = xlOn Then
Call YesOxygen
Else
Call Oxygen
End If

in addition to calling these macros, I want the checkbox to place "0" in a range of cells when checked (E280:E285), and when unchecked I want cells E280:E285 to display its normal formulas. Is there anyway to do this? Thank you for any help.

 

[Andrew Poulsom]

Can't you put code for that in your YesOxygen and Oxygen procedures?

 

[kwroche]

yes i can, I just don't know how I would do the code for that, I've tried a number of things but it won't work. Sorry, I should of clarified it more clearly what I need. I need the code for a macro to do what I talked about earlier.

 

[Andrew Poulsom]

Putting zero in the cells is easy:

Range("E280:E285").Value = 0

The formula would probably also be easy if I knew what it was.

An alternative is to change your formulas in E280:E285 so that they return 0 if the CheckBox's LinkedCell contains TRUE.

 

[kwroche]

ooo, thats a good idea.

Ok, tell me what to fix with this then...
=if(check box 62=true,0,"formula")
this doesn't work as I am unsure of how to check to see whether a check box value is on or not in a cell formula. Could you help me out on that one?

 

[Andrew Poulsom]

Set the CheckBox's LinkedCell property to some spare cell. Then use that cell in your IF formula.

 

[kwroche]

ahhh, thank you very much, that was a lot of help