Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing a cell value based on change in other field
I have two cells which can be either "Y" or "N". Both have list dropdowns
which are limited to Y or N and field cannot be left blank. These two fields are mutually exclusive to the extent that both cannot be "Y". They both can be "N". If the cell "B6" is set to Y, the cell "B7" must be set to ""N". If the end user then changes "B7" to "Y", then "B6" must be set to "N". Below is my code in the Worksheet "Inputs". If Target.Address = Range("B6") Then If Range("B6").Value = "Y" Then Range("B7").Value = "N" MsgBox "If Interest is in Arrears, you cannot Defer Interest." End If End If If Target.Address = Range("B7") Then If Range("B7").Value = "Y" Then Range("B6").Value = "N" MsgBox "If Interest is being Deferred, you cannot pay interest in arrears." End If End If So far neither seems to affect the other. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing a cell value based on change in other field
Put in the Active Sheet code module.
Private Sub Worksheet_Change(ByVal Target As Range) Range("B6:B7").ClearContents If Target = Range("B6") Then If UCase(Target.Value) = "Y" Then Range("B7").Value = "N" MsgBox "If Interest is in Arrears, you cannot Defer Interest." End If End If If Target = Range("B7") Then If UCase(Target.Value) = "Y" Then Range("B6").Value = "N" MsgBox "If Interest is being Deferred, you cannot pay interest in arrears." End If End If End Sub "Dkline" wrote: I have two cells which can be either "Y" or "N". Both have list dropdowns which are limited to Y or N and field cannot be left blank. These two fields are mutually exclusive to the extent that both cannot be "Y". They both can be "N". If the cell "B6" is set to Y, the cell "B7" must be set to ""N". If the end user then changes "B7" to "Y", then "B6" must be set to "N". Below is my code in the Worksheet "Inputs". If Target.Address = Range("B6") Then If Range("B6").Value = "Y" Then Range("B7").Value = "N" MsgBox "If Interest is in Arrears, you cannot Defer Interest." End If End If If Target.Address = Range("B7") Then If Range("B7").Value = "Y" Then Range("B6").Value = "N" MsgBox "If Interest is being Deferred, you cannot pay interest in arrears." End If End If So far neither seems to affect the other. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing a cell value based on change in other field
Take out the line Range("B6:B7").ClearContents.
However, if there is an entry of Y in cell B6, it will not change when Y is entered in B7 and B7 ends up with N. "Dkline" wrote: I have two cells which can be either "Y" or "N". Both have list dropdowns which are limited to Y or N and field cannot be left blank. These two fields are mutually exclusive to the extent that both cannot be "Y". They both can be "N". If the cell "B6" is set to Y, the cell "B7" must be set to ""N". If the end user then changes "B7" to "Y", then "B6" must be set to "N". Below is my code in the Worksheet "Inputs". If Target.Address = Range("B6") Then If Range("B6").Value = "Y" Then Range("B7").Value = "N" MsgBox "If Interest is in Arrears, you cannot Defer Interest." End If End If If Target.Address = Range("B7") Then If Range("B7").Value = "Y" Then Range("B6").Value = "N" MsgBox "If Interest is being Deferred, you cannot pay interest in arrears." End If End If So far neither seems to affect the other. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preventing a cell value based on change in other field
At one point I had it working by adding in the ClearContents. But now it
doesn't even trigger the debugger even though I have set breakpoints. I am stumped at this point. "JLGWhiz" wrote: Take out the line Range("B6:B7").ClearContents. However, if there is an entry of Y in cell B6, it will not change when Y is entered in B7 and B7 ends up with N. "Dkline" wrote: I have two cells which can be either "Y" or "N". Both have list dropdowns which are limited to Y or N and field cannot be left blank. These two fields are mutually exclusive to the extent that both cannot be "Y". They both can be "N". If the cell "B6" is set to Y, the cell "B7" must be set to ""N". If the end user then changes "B7" to "Y", then "B6" must be set to "N". Below is my code in the Worksheet "Inputs". If Target.Address = Range("B6") Then If Range("B6").Value = "Y" Then Range("B7").Value = "N" MsgBox "If Interest is in Arrears, you cannot Defer Interest." End If End If If Target.Address = Range("B7") Then If Range("B7").Value = "Y" Then Range("B6").Value = "N" MsgBox "If Interest is being Deferred, you cannot pay interest in arrears." End If End If So far neither seems to affect the other. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return value based on number of characters in cell / field | Excel Worksheet Functions | |||
calculated field based on data field | Excel Discussion (Misc queries) | |||
Change a pivot table field by typing into another cell | Excel Discussion (Misc queries) | |||
Update date field upon cell range change | Excel Worksheet Functions |