ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Preventing a cell value based on change in other field (https://www.excelbanter.com/excel-programming/387232-preventing-cell-value-based-change-other-field.html)

Dkline

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.

JLGWhiz

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.


JLGWhiz

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.


Dkline

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.



All times are GMT +1. The time now is 01:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com