If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change a cells value with a function
Here is my function contained in O74. C70 has "Water Heater" in it and
I73="2". Why doesn't N74 have FALSE in it? Also, is there a way to control a Forms checkbox with a function? =IF(C70="Water Heater",IF(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE")) -- Cheers, Ryan |
#2
|
|||
|
|||
Change a cells value with a function
A formula can't edit the value of another cell; it just returns a value to
the cell containing the formula. Try this formula in N74: =IF(C70="Water Heater",I73=39,I73=99) I think the answer to your second question is that if the linked cell of your checkbox contains a formula, like the one above, that returns TRUE or FALSE, it can be used to control the checkbox - until the first time anyone checks or unchecks the checkbox directly. Then the formula in the linked cell will be replaced with TRUE or FALSE. Hope this helps, Hutch "Ryan H" wrote: Here is my function contained in O74. C70 has "Water Heater" in it and I73="2". Why doesn't N74 have FALSE in it? Also, is there a way to control a Forms checkbox with a function? =IF(C70="Water Heater",IF(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE")) -- Cheers, Ryan |
#3
|
|||
|
|||
Change a cells value with a function
Is there a way to check a checkbox or optionbutton if a cell is calculated to
True or False? -- Cheers, Ryan "Tom Hutchins" wrote: A formula can't edit the value of another cell; it just returns a value to the cell containing the formula. Try this formula in N74: =IF(C70="Water Heater",I73=39,I73=99) I think the answer to your second question is that if the linked cell of your checkbox contains a formula, like the one above, that returns TRUE or FALSE, it can be used to control the checkbox - until the first time anyone checks or unchecks the checkbox directly. Then the formula in the linked cell will be replaced with TRUE or FALSE. Hope this helps, Hutch "Ryan H" wrote: Here is my function contained in O74. C70 has "Water Heater" in it and I73="2". Why doesn't N74 have FALSE in it? Also, is there a way to control a Forms checkbox with a function? =IF(C70="Water Heater",IF(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE")) -- Cheers, Ryan |
#4
|
|||
|
|||
Change a cells value with a function
You could do something with event code. For example, if A1 is the linked cell
for your checkbox, and C1 has a formula that evaluates to TRUE or FALSE, you could use a Worksheet_Change event like this: Private Sub Worksheet_Change(ByVal Target As Range) 'Target is the cell that changed anywhere on the sheet 'C1 has a formula that returns TRUE or FALSE 'A1 is the linked cell for a check box Select Case Range("C1").Value Case True: Range("A1").Value = True Case False: Range("A1").Value = False Case Else 'do nothing End Select End Sub When any cell is changed on the sheet, C1 is evaluated. If it is TRUE (or -1), A1 is set to TRUE and the checkbox is checked. If C1 evaluates to FALSE (or 0), A1 is set to FALSE and the checkbox is unchecked. The checkbox can still be checked & unchecked directly using the mouse. To add event code to a worksheet, right-click on the name tab of the sheet where you want this to work. Select "View code". The Visual Basic Editor (VBE) is displayed. Paste the code in the big empty white window. Close the VBE and the event code should be active for that sheet. If you are new to macros, this link to Jon Peltier's site may be helpful: http://peltiertech.com/WordPress/200...e-elses-macro/ Hope this helps, Hutch "Ryan H" wrote: Is there a way to check a checkbox or optionbutton if a cell is calculated to True or False? -- Cheers, Ryan "Tom Hutchins" wrote: A formula can't edit the value of another cell; it just returns a value to the cell containing the formula. Try this formula in N74: =IF(C70="Water Heater",I73=39,I73=99) I think the answer to your second question is that if the linked cell of your checkbox contains a formula, like the one above, that returns TRUE or FALSE, it can be used to control the checkbox - until the first time anyone checks or unchecks the checkbox directly. Then the formula in the linked cell will be replaced with TRUE or FALSE. Hope this helps, Hutch "Ryan H" wrote: Here is my function contained in O74. C70 has "Water Heater" in it and I73="2". Why doesn't N74 have FALSE in it? Also, is there a way to control a Forms checkbox with a function? =IF(C70="Water Heater",IF(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE")) -- Cheers, Ryan |
#5
|
|||
|
|||
Change a cells value with a function
I was trying to avoid using macros, but I guess I'm going to have to in this
case. I have to admit. I've been writing macros for 4 years now and feel very comfortable doing so, but I don't know much when it comes to writing formulas. Thanks for the helps! -- Cheers, Ryan "Tom Hutchins" wrote: You could do something with event code. For example, if A1 is the linked cell for your checkbox, and C1 has a formula that evaluates to TRUE or FALSE, you could use a Worksheet_Change event like this: Private Sub Worksheet_Change(ByVal Target As Range) 'Target is the cell that changed anywhere on the sheet 'C1 has a formula that returns TRUE or FALSE 'A1 is the linked cell for a check box Select Case Range("C1").Value Case True: Range("A1").Value = True Case False: Range("A1").Value = False Case Else 'do nothing End Select End Sub When any cell is changed on the sheet, C1 is evaluated. If it is TRUE (or -1), A1 is set to TRUE and the checkbox is checked. If C1 evaluates to FALSE (or 0), A1 is set to FALSE and the checkbox is unchecked. The checkbox can still be checked & unchecked directly using the mouse. To add event code to a worksheet, right-click on the name tab of the sheet where you want this to work. Select "View code". The Visual Basic Editor (VBE) is displayed. Paste the code in the big empty white window. Close the VBE and the event code should be active for that sheet. If you are new to macros, this link to Jon Peltier's site may be helpful: http://peltiertech.com/WordPress/200...e-elses-macro/ Hope this helps, Hutch "Ryan H" wrote: Is there a way to check a checkbox or optionbutton if a cell is calculated to True or False? -- Cheers, Ryan "Tom Hutchins" wrote: A formula can't edit the value of another cell; it just returns a value to the cell containing the formula. Try this formula in N74: =IF(C70="Water Heater",I73=39,I73=99) I think the answer to your second question is that if the linked cell of your checkbox contains a formula, like the one above, that returns TRUE or FALSE, it can be used to control the checkbox - until the first time anyone checks or unchecks the checkbox directly. Then the formula in the linked cell will be replaced with TRUE or FALSE. Hope this helps, Hutch "Ryan H" wrote: Here is my function contained in O74. C70 has "Water Heater" in it and I73="2". Why doesn't N74 have FALSE in it? Also, is there a way to control a Forms checkbox with a function? =IF(C70="Water Heater",IF(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE")) -- Cheers, Ryan |
Thread Tools | |
Display Modes | |
|
|