Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clean-Up Cascading Data Validation List Values
I have two dependent data validation lists. The first list (in cell A1)
determines the values on the second list (in cell A2). When the value selected in cell A1 is changed, the following code blanks out any value already selected in cell A2. The code works when the value in cell A1 is changed to a new value, but it doesn't work when the value in cell A1 is deleted or blanked out. How can I fix this? Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "A1" Then ActiveSheet.Range("A2").Value = "" End If End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clean-Up Cascading Data Validation List Values
The code you posted won't run the "Then" portion of your code. The .address
will include the $ signs unless you do something different. Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "$A$1" Then me.Range("A2").Value = "" End If End Sub If this doesn't help, you may want to copy directly from the VBE and paste into your followup message. Lisa C. wrote: I have two dependent data validation lists. The first list (in cell A1) determines the values on the second list (in cell A2). When the value selected in cell A1 is changed, the following code blanks out any value already selected in cell A2. The code works when the value in cell A1 is changed to a new value, but it doesn't work when the value in cell A1 is deleted or blanked out. How can I fix this? Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "A1" Then ActiveSheet.Range("A2").Value = "" End If End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clean-Up Cascading Data Validation List Values
This is my actual code. It runs the "Then" portion but only when I change
the value in $L$3 to another value. It doesn't work when I delete the value in $L$3. Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "$L$3" Then ActiveSheet.Range("D21:D35").Value = "" End If End Sub "Dave Peterson" wrote: The code you posted won't run the "Then" portion of your code. The .address will include the $ signs unless you do something different. Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "$A$1" Then me.Range("A2").Value = "" End If End Sub If this doesn't help, you may want to copy directly from the VBE and paste into your followup message. Lisa C. wrote: I have two dependent data validation lists. The first list (in cell A1) determines the values on the second list (in cell A2). When the value selected in cell A1 is changed, the following code blanks out any value already selected in cell A2. The code works when the value in cell A1 is changed to a new value, but it doesn't work when the value in cell A1 is deleted or blanked out. How can I fix this? Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "A1" Then ActiveSheet.Range("A2").Value = "" End If End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clean-Up Cascading Data Validation List Values
Dear Lisa
You must be doing the deletion after selecting a range which includes L3. Please use the Application.Intersect as below Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Application.Intersect(Target, Range("$L$3")) Is Nothing Then ActiveSheet.Range("D21:D35").Value = "" End If End Sub If this post helps click Yes --------------- Jacob Skaria "Lisa C." wrote: This is my actual code. It runs the "Then" portion but only when I change the value in $L$3 to another value. It doesn't work when I delete the value in $L$3. Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "$L$3" Then ActiveSheet.Range("D21:D35").Value = "" End If End Sub "Dave Peterson" wrote: The code you posted won't run the "Then" portion of your code. The .address will include the $ signs unless you do something different. Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "$A$1" Then me.Range("A2").Value = "" End If End Sub If this doesn't help, you may want to copy directly from the VBE and paste into your followup message. Lisa C. wrote: I have two dependent data validation lists. The first list (in cell A1) determines the values on the second list (in cell A2). When the value selected in cell A1 is changed, the following code blanks out any value already selected in cell A2. The code works when the value in cell A1 is changed to a new value, but it doesn't work when the value in cell A1 is deleted or blanked out. How can I fix this? Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "A1" Then ActiveSheet.Range("A2").Value = "" End If End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clean-Up Cascading Data Validation List Values
Another variation:
Option Explicit Private Sub worksheet_change(ByVal target As Range) If intersect(target, me.range("L3")) is nothing then 'do nothing else me.Range("D21:D35").Value = "" End If End Sub Using ActiveSheet may cause trouble if your code gets more complicated. The Me keyword refers to the worksheet that owns the code (where you made the changes). Lisa C. wrote: This is my actual code. It runs the "Then" portion but only when I change the value in $L$3 to another value. It doesn't work when I delete the value in $L$3. Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "$L$3" Then ActiveSheet.Range("D21:D35").Value = "" End If End Sub "Dave Peterson" wrote: The code you posted won't run the "Then" portion of your code. The .address will include the $ signs unless you do something different. Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "$A$1" Then me.Range("A2").Value = "" End If End Sub If this doesn't help, you may want to copy directly from the VBE and paste into your followup message. Lisa C. wrote: I have two dependent data validation lists. The first list (in cell A1) determines the values on the second list (in cell A2). When the value selected in cell A1 is changed, the following code blanks out any value already selected in cell A2. The code works when the value in cell A1 is changed to a new value, but it doesn't work when the value in cell A1 is deleted or blanked out. How can I fix this? Option Explicit Private Sub worksheet_change(ByVal target As Range) If target.Address = "A1" Then ActiveSheet.Range("A2").Value = "" End If End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
CASCADING LIST BOX | Excel Discussion (Misc queries) | |||
clean duplicates in list | Excel Discussion (Misc queries) | |||
Create a cascading list box using Excel | Excel Discussion (Misc queries) |