"Ambiguous name detected: Worksheet_Change" when adding 2 change events codes on the same sheet

[charliemike9285]

Hello, I am very new to VBAs and have figured out how to do these two functions separately but not how to combine them so they will both work on one sheet.
Putting them both on the same Project sheet I get the error message: Ambiguous name detected: Worksheet_Change (See image below)
My primary drop-down list is in Column F and my dependent drop down is in column H. The rows for the table are 2:100.
Any help would be greatly appreciated, thanks!

First change event is to Make multiple selections in a drop-down list (Column H):

Option Explicit


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("H:H")) 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 & ", " & vbNewLine & Newvalue

Else:

Target.Value = Oldvalue

End If

End If

End If

End If

Application.EnableEvents = True

Exitsub:

Application.EnableEvents = True

End Sub



Second change event is to Reset Dependent Drop-down for entire column (Column H):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 6 Then 'Column F

If Target.Row >= 2 And Target.Row <= 100 Then

Cells(Target.Row, 8) = "Please Select..."

End If

End If

End Sub


See below :

Again these codes work separately, but I don't know how to make it so both codes will work on the same sheet.

 

[Peter_SSs]

You can only have one of these codes so you need to combine them. Untested, but try this.

BTW, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy.
My signature block below has more details.

 

[charliemike9285]

You can only have one of these codes so you need to combine them. Untested, but try this.

View attachment 83366

BTW, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy.
My signature block below has more details.

So that almostttt worked perfectly! When I change the selection in the original column (F), it resets to "Select all..." in column H, however, when I go to make a new selection in Column H, it does not get rid of the "Please select..." (Example below)
Is there any way to get that to go away once a new selection is made?
(Not sure if I used the </> correctly but I tried)

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub[/CODE]
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 & ", " & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If

If Target.Column = 6 Then 'Column F
If Target.Row >= 2 And Target.Row <= 100 Then
Cells(Target.Row, 8) = "Please Select..."
End If
End If

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[charliemike9285]

Sorry, the above had both an error in what i pasted and apparently formatting it did not work I hope this correction works

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("H:H")) 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 & ", " & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If

If Target.Column = 6 Then 'Column F
    If Target.Row >= 2 And Target.Row <= 100 Then
        Cells(Target.Row, 8) = "Please Select..."
    End If
End If

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[Peter_SSs]

I hope this correction works

It did, thanks.

The Application.EnableEvents = True right near the start of the code is
a) Pointless since if EnableEvents was not True then the code would not have been triggered in the first place, and
b) Best changed to False so that if/when this code alters any cells in the worksheet, this code is not pointlessly re-triggered.

I have just made a few changes, including combining the two original codes a little better so the column F check does not need to be done if column H was already found to be true and processed.
Give this version a try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = False
On Error GoTo Exitsub
If Not Intersect(Target, Range("H:H")) 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 = "" Or Oldvalue = "Please Select..." 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
ElseIf Target.Column = 6 Then 'Column F
    If Target.Row >= 2 And Target.Row <= 100 Then
        Cells(Target.Row, 8) = "Please Select..."
    End If
End If

Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[charliemike9285]

Thank you so much! This fixed it and made it exactly how I envisioned it to be.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[charliemike9285]

I'm wanting to protect certain columns from being edited (which I know how to do), however, when I do this, it prevents the VBA code from running. Is there a way I can protect the cells/sheet and still have the VBA code function properly?

 

[Peter_SSs]

Probably the easiest way would be to have the code unprotect the sheet, do what it needs to do then protect it again.
Something like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
ActiveSheet.Unprotect Password:="abc" '<- Edit to your password
Application.EnableEvents = False
On Error GoTo Exitsub
If Not Intersect(Target, Range("H:H")) 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 = "" Or Oldvalue = "Please Select..." 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
ElseIf Target.Column = 6 Then 'Column F
    If Target.Row >= 2 And Target.Row <= 100 Then
        Cells(Target.Row, 8) = "Please Select..."
    End If
End If

Application.EnableEvents = True
Exitsub:
ActiveSheet.Protect Password:="abc" '<- Edit to your password
Application.EnableEvents = True
End Sub

 

[charliemike9285]

Thank you, that worked! I appreciate all your help with this and being patient with a newbie.