Nov 18 2022 10:44 AM - edited Nov 18 2022 11:25 AM
Hello. I've placed some checkboxes into my Excel spreadsheet and would like to apply a Macro to it that will do the following:
If checkbox is CHECKED and linked to cell E7 in format control, then multiply the values of cells B7 and C7 and output that answer in cell E7. But if the checkbox is NOT CHECKED, then E7 should equal 0.
(For example: * if checkbox is placed in cell D7 and is Checked* and the contents of cell B7 is 10 and the contents of cell C7 is 2 - then E7 should display the answer, 20. But if there's NO CHECK in the box, the value should stay Zero. I want to remove the text name on the Checkbox (ie: Check box 18) - I can remove the text, but not sure how to do that and still somehow apply Macros to it.
Any idea how I would put this in the Module?
Nov 18 2022 02:30 PM
Nov 18 2022 06:58 PM
@mtarlerThank you so much for helping me, I greatly appreciate it. However, it still does not seem to work. If cell D7 that contains the checkbox is checked, then the "linked" cell (E7) continues to be TRUE or FALSE and does not keep "=D7*C7*B7" inside the cell once checked. I think that's why I was assuming a Macro would work here. I've attached an example spreadsheet to show what's going on. When the box is checked, I'd like "FALSE" in cell E7 to be a Zero, but if "TRUE" I'd like the output to be Product of B7 times C7.
Nov 18 2022 07:18 PM
Solution@Rashaud35 it doesn't matter where the checkbox is located. you want to link to some 'other' cell and not the one you want the formula in. so in the attached I changed the 1st row to be 'linked' to D7 so in E7 is the formula =B7*C7*D7
You can then "hide" the true/false text by changing the font color to be same as fill color (you could also use custom number formatting if needed)
Nov 18 2022 07:32 PM - edited Nov 18 2022 07:33 PM
@mtarler Ohhh okay, I see what you're saying now. I was missing the fact that True/False output needed to be in a totally different cell than the cell I wanted my output answer to be in. I can change the True/False font color (white) match the background of the cell so this will suffice. Thank you so much again!
Nov 18 2022 11:12 PM
- I hope I have solved the problem you highlighted. Cell addresses are not the same as they were in your Excel file.
- Attached are two pictures to give you a clear view of what i did.
1. Added another column (F4)
2. Type this formula =IF(E4,B4*C4,"0") into cell F4, and drag it down.
3. Hide column E that contains the true, false strings.
4. Replace the values in cells B4 and C4, and let the checkbox in cell D4 be checked or unchecked. Confirm the answers as expected.
Figure 1- Additional column in cell F4 with new values in cells B4 & C4
Figure 2- Column E4 is hidden
Hope this clears up the problem
Regards
Nov 20 2022 01:49 PM
Nov 18 2022 07:18 PM
Solution@Rashaud35 it doesn't matter where the checkbox is located. you want to link to some 'other' cell and not the one you want the formula in. so in the attached I changed the 1st row to be 'linked' to D7 so in E7 is the formula =B7*C7*D7
You can then "hide" the true/false text by changing the font color to be same as fill color (you could also use custom number formatting if needed)