Excel VBA - Select Multiple Values Drop Down Validation

Question

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!

Scott Holtzman · Accepted Answer · 2018-04-26 14:22:31Z

1

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

edited Apr 26, 2018 at 14:22

answered Apr 26, 2018 at 12:03

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Smith Over a year ago

Thank you for your reply unfortunatly i get a syntax error when replacing the line in the code as mentioned.

Tom Smith Over a year ago

Would you know why this is? Thank you

Collectives™ on Stack Overflow

Excel VBA - Select Multiple Values Drop Down Validation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related