I have an Excel document with five sheets. On the second sheet there are three codes under Private Sub Worksheet_Change (ByVal Target As Range) that goes like this :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub 'only handling one-cell changes
DoMessage Target
HideShowRows Target
DropDown Target
End Sub
The DoMessage Target and HideShowRows are good, they work perfectly. DropDown target doesn’t work. This code is supposed to allow several items in a dropdown list to be chosen by the user simultaneously and non-repetitively. Here’s the code for this one :
Sub DropDown(Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("H11")) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
What’s wrong with the code ?