Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost of Question on Preventing 2 cells from having the same valu
I had posted this question last week. JLGWhiz kindly posted a solution but I
cannot get it to work 100% of the time. So I am making another appeal for help. My original post was: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost of Question on Preventing 2 cells from having the same valu
You don't say what "I cannot get it to work 100% of the time" means, so
I'm not sure what's wrong with the code. Here's another way: Const csMsg1 As String = _ "If Interest is in Arrears, you cannot Defer Interest." Const csMsg2 As String = _ "If Interest is being Deferred, you cannot pay interest in arrears." Dim nSecond As Long With Range("B6:B7") If Not Intersect(Target(1), .Cells) Is Nothing Then If Target(1).Value = "Y" Then nSecond = CLng(Target.Address(False, False) = "B7") With .Item(2 + nSecond) If .Value = "Y" Then .Value = "N" MsgBox Choose(2 + nSecond, csMsg2, csMsg1) End If End With End If End If End With In article , Dkline wrote: I had posted this question last week. JLGWhiz kindly posted a solution but I cannot get it to work 100% of the time. So I am making another appeal for help. My original post was: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost of Question on Preventing 2 cells from having the same valu
Hi Dkline -
If Target.Address = "$B$6" 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 = "$B$7" 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 --- Jay "Dkline" wrote: I had posted this question last week. JLGWhiz kindly posted a solution but I cannot get it to work 100% of the time. So I am making another appeal for help. My original post was: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost of Question on Preventing 2 cells from having the same
So it was simple as making the Target.Address an absolute?!
I need a proverbial smack upside the head. I say so because I had already specified a couple of target.addesses in code that preceded this portion of the code. "Jay" wrote: Hi Dkline - If Target.Address = "$B$6" 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 = "$B$7" 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 --- Jay "Dkline" wrote: I had posted this question last week. JLGWhiz kindly posted a solution but I cannot get it to work 100% of the time. So I am making another appeal for help. My original post was: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost of Question on Preventing 2 cells from having the same
Thank you for your prompt and correct reply. It works perfectly. I was having
trouble with - on occasion - that when I clicked Y on the Defer button, that it would not change the Arrears entry 100% of the time. It seemed to have a mind of its own or gremlins had invaded my computer. I was running out of hair to pull out. Thanks again. "JE McGimpsey" wrote: You don't say what "I cannot get it to work 100% of the time" means, so I'm not sure what's wrong with the code. Here's another way: Const csMsg1 As String = _ "If Interest is in Arrears, you cannot Defer Interest." Const csMsg2 As String = _ "If Interest is being Deferred, you cannot pay interest in arrears." Dim nSecond As Long With Range("B6:B7") If Not Intersect(Target(1), .Cells) Is Nothing Then If Target(1).Value = "Y" Then nSecond = CLng(Target.Address(False, False) = "B7") With .Item(2 + nSecond) If .Value = "Y" Then .Value = "N" MsgBox Choose(2 + nSecond, csMsg2, csMsg1) End If End With End If End If End With In article , Dkline wrote: I had posted this question last week. JLGWhiz kindly posted a solution but I cannot get it to work 100% of the time. So I am making another appeal for help. My original post was: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference a range of cells depending on another cell valu | Excel Discussion (Misc queries) | |||
Repost of VBA Code/Lookup question | Excel Worksheet Functions | |||
Repost Of ealier Question | Excel Programming | |||
Question - Repost | Excel Programming | |||
Excel 2000 math question, repost | Excel Programming |