VBA code help, changes in specific cells trigger changes

[Ben2000]

Hi all,

I have dependent dropdown lists and need to have the corresponding dropdown list cleared when the primary selection changes.

I am trying to use VBA code to do this and went through some programming material, but I cannot see how to reference these. I.e.

Anything changes in Cell D16 onwards, then the corresponding cell in E should to state "Please select..."

So, if D16 changes, then E16 is automatically changed to "Please select".

Any help possible?

Many thanks!

 

[JoeMo]

What do you want in E16 if a value in D16 is deleted?

 

[Ben2000]

Hello Joe,

Thanks for looking into this. It is like this:

D16 has product group, say fruits, and vegetables
E16 has the specific item, e.g. specific fruit OR specfic vegetables

So, basically, if D16 changes from Fruits to Vegetables, then I want the adjoining cell E16 to basically reset itself to show, "Please select" a new value.

And then I am using conditional formatting to highlight all cells that have "Please select" as the value. Basically trying to ensure that peaches don't land up being vegetables.

 

[JoeMo]

You didn't answer my question so I'll assume you want E16 to be blank if the user deletes the value in D16.
This code is for the sheet holding the dropdowns. Right-click the sheet tab, choose 'View Code' and paste the code into the white space that appears in the VBE window.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, lRw As Long
lRw = Target(Target.Count).Row
If lRw < 16 Then Exit Sub
If Not Intersect(Target, Range("D16:D" & lRw)) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, Range("D16:D" & lRw))
        If Not IsEmpty(c) Then
            Cells(c.Row, c.Column + 1).Value = "Please select"
        Else
            Cells(c.Row, c.Column + 1).Value = ""
        End If
    Next c
    Application.EnableEvents = True
End If
End Sub

 

[Ben2000]

Thanks a ton Joe, much appreciated, works like a charm! BTW, any good resources for learning the art of scripting that you have done?

 

[JoeMo]

Thanks a ton Joe, much appreciated, works like a charm! BTW, any good resources for learning the art of scripting that you have done?

You are welcome - thanks for the reply. There are lots of tutorials on the internet- google 'VBA for Excel'.