VBA Multiselect & Single select lists

[ExcelHelpNeeded99]

Can anyone help me see where Im going wrong. I only want multi select dropdown lists in columns 10,22,46,47,48, I need the rest to be single select or text only. I used the below vba code and altered it for my columns but every column is multi select. I am new to this forum and appreciate the help. Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated: 2016/4/12
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    
    If (Target.Column = 10 Or Target.Column = 22 Or Target.Column = 46 Or Target.Column = 47 Or Target.Column = 48) 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
[Code/]

 

[MickG]

Try removing the bits of code in red (Exit Sub) , and then add an "End if" at the bottom of the code, just before "End Sub"

If (Target.Column = 10 Or Target.Column = 22 Or Target.Column = 46 Or Target.Column = 47 Or Target.Column = 48) Then [SIZE=3][B][COLOR=#FF0000]Exit Sub[/COLOR][/B][/SIZE]

 

[ExcelHelpNeeded99]

Hi Mick I tried that but now it made all dropdown columns single select instead of multi select for relevant columns ( i also updated column refs) ,any other ideas? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated: 2016/4/12
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String


If (Target.Column = 5 Or Target.Column = 17 Or Target.Column = 41 Or Target.Column = 42 Or Target.Column = 43) Then
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 If
End Sub



[\Code]

 

[MickG]

Try this example:-
NB:- Change column References to suit!!

https://app.box.com/s/wy9qbrsmz1lsv52j40kkfri00lrhfsbk

 

[ExcelHelpNeeded99]

Thanks, your example is what I want, only thing is, my lookup values are in another tab and there are different lists for each column. How would I modify for that? I really appreciate the help!

 

[ExcelHelpNeeded99]

Just a note- I know how to update the data validation lists from the other tab its just the vba piece Im wondering how to tell it they are on another tab. Thanks

 

[ExcelHelpNeeded99]

This fixed my issue- Thank you!!!