Need VBA to run each time selection is made in data validation list

[General Ledger]

Dear All,

Using Excel 2003: I have a macro that I want to run every time a selection is made from a drop down list.

Cell B4 has data validation list. I want the below code to run every time the user makes a selection from the drop down list in B4. Currently, it only runs when the active cell is not B4 and then B4 is selected (when the selection changes).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("B4")) Is Nothing) Then
If Len(Trim(Range("B4"))) < 5 Then
ActiveSheet.Range("B6") = "No template"
Else: ActiveSheet.Range("B6") = "Template"
End If
End If
End Sub

Best regards,

GL

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "B4" Then
    If Len(Trim(Range("B4").Value)) < 5 Then
        Me.Range("B6").Value = "No template"
    Else
        Me.Range("B6").Value = "Template"
    End If
End If
End Sub

 

[General Ledger]

VoG,

Once again, you provided the Perfect solution within nano-seconds.

Thanks so much,

GL