VBA for Multliselect dropdown (data validation)

[bmontoni]

Hi all!

I am using the code below to get cell K2 to become a multi select dropdown (not allow duplicates). How would I change the code to have K2 and all cells below it to also become multi select drop downs?

Thank in advance!


Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from Online Excel Tips & Tutorials
' To make mutliple selections in a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$K$2" 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

 

[rakesh seebaruth]

You can modify the code to make all cells from K2 down to the last used cell in column K multi select dropdowns by changing the following lines:

Old code:

If Target.Address = "$K$2" Then

New code:

If Target.Column = 11 And Target.Row >= 2 And Target.Row <= Range("K" & Rows.Count).End(xlUp).Row Then

 

[bmontoni]

works perfectly - thank you!