CODEWITHSUNDEEP
vbaexcel
Tom Smith
Tom Smith

Reputation: 1

Excel VBA - Select Multiple Values Drop Down Validation

I would appreciate some assistance with a problem i am having. The following code sample allows me to select multiple values from a dropdown list however i need the target.address to be every row within column S.

I am unsure how to change the target address so that it is the range s10 onwards (ie S10-S150)

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$S10" Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = "" Then GoTo Exitsub Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Thank you!

Upvotes: 0

Views: 381

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

You can test to see if the affected range is within another range like this:

If Not Intersect(Target, Range("S10:S150")) Is Nothing Then

The line above would replace this line in your original code:

If Target.Address = "$S10" Then

Upvotes: 1

Related Questions