Add a Range to a VBA - Multi-Choice Drop Down

[Geordiegirl83]

Hi All,

I'm sure this is a simple fix but I cant get it to work at all. I have the below code which allows multi choice to all of my drop downs on a form i have created, but I only want the drop downs in Col. E & H to be multi-choice and I would like all other drop downs in the sheet to be single choice options.

At the moment I can only get them to ALL be multi choice or ALL be single choice ... HELP!

CODE:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String

If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False

If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" Then
If xValue2 <> "" Then
If xValue1 = xValue2 Or _
InStr(1, xValue1, ", " & xValue2) Or _
InStr(1, xValue1, xValue2 & ",") Then
Target.Value = xValue1
Else
Target.Value = xValue1 & ", " & xValue2
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

 

[DanteAmor]

Hi @Geordiegirl83
Thanks for posting on the forum.

After this line:

If Target.Count > 1 Then Exit Sub

Add this line:

If Intersect(Target, Range("E:H")) Is Nothing Then Exit Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Geordiegirl83]

Hi @Geordiegirl83
Thanks for posting on the forum.

After this line:

If Target.Count > 1 Then Exit Sub

Add this line:

If Intersect(Target, Range("E:H")) Is Nothing Then Exit Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hi & Thanks for your reply.

This kind of works for most part, but I want only Col E & Col H to be multi-choice - I have a drop down in F also, which i want to be single option only, as its a dependant drop down for another cell.

 

[DanteAmor]

but I want only Col E & Col H to be multi-choice

I'm sorry it was my fault

Use this line:

If Intersect(Target, Range("E:E, H:H")) Is Nothing Then Exit Sub

 

[Geordiegirl83]

Perfect thank you