Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox linked to calcs
Hi, I'm trying to use a checkbox to either include a certain data point or
exclude it. If checkbox is checked, n=n-a, if checkbox is unchecked, n. I tried running the following code, but it doesn't work. Can someone please help? Dim withMobile As CheckBox If withMobile.Value = True Then Range("U66").Select ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]" Selection.AutoFill Destination:=Range("U66:U99"), Type:=xlFillDefault Range("V66").Select ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]" Selection.AutoFill Destination:=Range("V66:V99"), Type:=xlFillDefault Range("W66").Select ActiveCell.FormulaR1C1 = _ "=IF(FISCAL_YTD=""YES"",RC[-2]-RC[-1],IFERROR(RC[-2]-RC[-1],0))-RC[108]" Selection.AutoFill Destination:=Range("W66:W99"), Type:=xlFillDefault Range("L66").Select ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]" Selection.AutoFill Destination:=Range("L66:L99"), Type:=xlFillDefault ElseIf withMobile.Value = False Then Range("U66").Select ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]" Selection.AutoFill Destination:=Range("U66:U99"), Type:=xlFillDefault Range("V66").Select ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]" Selection.AutoFill Destination:=Range("V66:V99"), Type:=xlFillDefault Range("W66").Select ActiveCell.FormulaR1C1 = _ "=IF(FISCAL_YTD=""YES"",RC[-2]-RC[-1],IFERROR(RC[-2]-RC[-1],0))" Selection.AutoFill Destination:=Range("W66:W99"), Type:=xlFillDefault Range("L66").Select ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]" Selection.AutoFill Destination:=Range("L66:L99"), Type:=xlFillDefault End If End Sub Thank you, MK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox linked to calcs
Check boxes sit on top of the worksheet an cannot directly effect functions on the worksheet. You need to set the Linked Cell property of the check box to add either True or False to a new column in the worksheet. So if you put a true or false in colmn b then modify you formula as follows from ActiveCell.FormulaR1C1 = "=RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108]" to ActiveCell.FormulaR1C1 = "=if(RC[2]=True,RC[60]+RC[90]+RC[102]+RC[114]+RC[126]-RC[108],0)" -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183466 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox linked to calcs | Excel Programming | |||
font color with checkbox linked cel | Excel Discussion (Misc queries) | |||
CheckBox / Linked Cell Problem | Excel Programming | |||
Checkbox name linked to a cell | Excel Discussion (Misc queries) | |||
checkbox linked to a cell | Excel Programming |