Excel VBA select value from Drop down list

[Angel88]

Hi,

In cell A1 and cell E1, i have added a drop down list using the validation list type.

i would like to change the E1 value to "All" from the drop down list every time i select a value from cell A1 drop down list.

how do i do that ?

thanks

 

[AnAnalyst]

Hi there,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, [A1]) Is Nothing Then
        [E1].Value = "All"
    End If
End Sub

Paste this in the sheet module of the sheet that contains the cells you are referring to.

 

[Angel88]

Great...Thanks a Lot !!!

It works perfectly fine....

 

[jim may]

This is ONE OF THOSE "GOTCHAS" - Where a change in the macro triggers another change, requireing these 2 additional lines (in Red)..


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, [A1]) Is Nothing Then
[E1].Value = "All"
End If
Application.EnableEvents = True
End Sub