VBA Code not working for full colum multiple selecton Drop-down list

[Leicester City Fox]

Hi there

Need some help I put the vba in for a cell to select multiple drop down options and it works .

But when I change it so you can do this to the whole column it don't work and it still only work in the original cell.

Its Column T which is Column number 20 but start in cell T8:

Sub DropSelMult()


Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Column = 20 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



Any help in order to get this t work

Many Thanks

Leicester Fox

 

[MickG]

You need to have the code in a Worksheet Event as below.
Also ensure That the Application.EnableEvents , in code, is set to true.
If unsure run a bit of code as below:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Oldvalue [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
 [COLOR="Navy"]Dim[/COLOR] Newvalue [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

 [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] GoTo Exitsub
 [COLOR="Navy"]If[/COLOR] Target.Column = 20 [COLOR="Navy"]Then[/COLOR]
 [COLOR="Navy"]If[/COLOR] Target.SpecialCells(xlCellTypeAllValidation) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
GoTo Exitsub
Else: [COLOR="Navy"]If[/COLOR] Target.Value = "" [COLOR="Navy"]Then[/COLOR] GoTo Exitsub [COLOR="Navy"]Else[/COLOR]
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
[COLOR="Navy"]If[/COLOR] Oldvalue = "" [COLOR="Navy"]Then[/COLOR]
Target.Value = Newvalue
[COLOR="Navy"]Else[/COLOR]
Target.Value = Oldvalue & ", " & Newvalue
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]End[/COLOR] If
 Application.EnableEvents = True
Exitsub:
 Application.EnableEvents = True
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

EnableEvent Code:-

Private Sub CommandButton1_Click()
Application.EnableEvents = True
End Sub

Regards Mick