Using Multiple Private Sub Worksheet_Change(ByVal Target As Range)

[NickF72]

Hi have just stumbled across the fact I can't have 2x Private Sub Worksheet_Change(ByVal Target As Range) in the same macro.

Noob question but is it possible to modify the code so that the following work:

Section 1:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("G8:H8")) Is Nothing 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
            If InStr(1, Oldvalue, Newvalue) = 0 Then
               Target.Value = Oldvalue & ", " & Newvalue
         Else:
            Target.Value = Oldvalue
         End If
      End If
   End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Section 2:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String

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

* section 2 will permit me to have multi select ability on a data validation drop down list.

Advice gratefully received as I am stumped?

 

[RoryA]

Welcome to the forum.

First off, you should know that if you apply SpecialCells to a single cell, it will actually be applied to the entire sheet, so your If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing is probably not checking what you actually want it to.

With that said, it appears that you just need to add B19 into the range specified in the first code, since you are doing the same thing for both ranges:

If Not Intersect(Target, Range("B19,G8:H8")) Is Nothing Then

 

[NickF72]

Thanks Rory - will amend code and test

 

[NickF72]

Perfect that was just the change required, thank you