Ambiguous Name Detected: Worksheet_Change error

[kiwikiki718]

Hi I have 2 worksheet_change events that I am trying to run on worksheet 1 but I keep receiving a error stating "ambiguous name detected: worksheet_change".. please help. see below code

Private Sub worksheet_change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("A3,A13,A23,A33,A43,A53,A63,A87,A97,A107,A117,A127,A137,A147,B3,B13,B23,B33,B43,B53,B63,B87,B97,B107,B117,B127,B137,B147")) Is Nothing Then Exit Sub
If Target = "" Then Target = 0
Application.ScreenUpdating = False
End Sub

Private Sub worksheet_change(ByVal Target As Range)

If Application.CountIf(Range("J3:J12"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J13:J22"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If


If Application.CountIf(Range("J23:J32"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J33:J42"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J43:J52"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J53:J62"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J63:J72"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J87:J96"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J97:J106"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J107:J116"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J117:J126"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J127:J136"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J137:J146"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

If Application.CountIf(Range("J147:J156"), Target) > 1 Then
MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
Target.Value = ""
End If

End Sub

 

[kiwikiki718]

This worked! the only issue is with the second code.. the 10th cell in all the ranges I am able to put in a duplicate value without getting the error message.

 

[HaHoBe]

Hi kiwikiki718,

you are right (I didn't check for the last cell). Please use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngStart As Long
  Dim varToCheck As Variant
  Dim lngCounter As Long
  Dim lngHowOften As Long

  If Target.CountLarge > 1 Then Exit Sub
  'Check which column is altered
  Select Case Target.Column
    Case 1 To 2
      'only if certain cells in Column A and B are altered, take action
      If Not Intersect(Target, Range("A3,A13,A23,A33,A43,A53,A63,A87,A97,A107,A117,A127,A137,A147,B3,B13,B23,B33,B43,B53,B63,B87,B97,B107,B117,B127,B137,B147")) Is Nothing Then
        Application.EnableEvents = False
        If Target = "" Then Target = 0
        Application.EnableEvents = True
      End If
    Case 10
      'first range in Column J
      If Not Intersect(Target, Range("J3:J72")) Is Nothing Then
        If Target.Value <> 0 Then
          lngStart = WorksheetFunction.RoundDown((Target.Row - 3) / 10, 0)
          If WorksheetFunction.CountIf(Cells(3 + lngStart * 10, 10).Resize(10, 1), Target) > 1 Then
            Application.EnableEvents = False
            MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
            Target.Value = ""
            Application.EnableEvents = True
          End If
        End If
      ElseIf Not Intersect(Target, Range("J87:J156")) Is Nothing Then
        If Target <> 0 Then
          lngStart = WorksheetFunction.RoundDown((Target.Row - 87) / 10, 0)
          If WorksheetFunction.CountIf(Cells(87 + lngStart * 10, 10).Resize(10, 1), Target) > 1 Then
            Application.EnableEvents = False
            MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
            Target.Value = ""
            Application.EnableEvents = True
          End If
        End If
      End If
    Case Else
  End Select

End Sub

You will need to update the number of rows which will be substracted in order to get the proper range to work with as in the first code the last row of a range would technically be included to the next range as it would return a different number ( rows 3 to 11 will rounddown to 0 while 12 will be 1 which is corrected in the last code so that 3 to 12 will be 0).

Holger

 

[kiwikiki718]

Hi kiwikiki718,

you are right (I didn't check for the last cell). Please use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngStart As Long
  Dim varToCheck As Variant
  Dim lngCounter As Long
  Dim lngHowOften As Long

  If Target.CountLarge > 1 Then Exit Sub
  'Check which column is altered
  Select Case Target.Column
    Case 1 To 2
      'only if certain cells in Column A and B are altered, take action
      If Not Intersect(Target, Range("A3,A13,A23,A33,A43,A53,A63,A87,A97,A107,A117,A127,A137,A147,B3,B13,B23,B33,B43,B53,B63,B87,B97,B107,B117,B127,B137,B147")) Is Nothing Then
        Application.EnableEvents = False
        If Target = "" Then Target = 0
        Application.EnableEvents = True
      End If
    Case 10
      'first range in Column J
      If Not Intersect(Target, Range("J3:J72")) Is Nothing Then
        If Target.Value <> 0 Then
          lngStart = WorksheetFunction.RoundDown((Target.Row - 3) / 10, 0)
          If WorksheetFunction.CountIf(Cells(3 + lngStart * 10, 10).Resize(10, 1), Target) > 1 Then
            Application.EnableEvents = False
            MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
            Target.Value = ""
            Application.EnableEvents = True
          End If
        End If
      ElseIf Not Intersect(Target, Range("J87:J156")) Is Nothing Then
        If Target <> 0 Then
          lngStart = WorksheetFunction.RoundDown((Target.Row - 87) / 10, 0)
          If WorksheetFunction.CountIf(Cells(87 + lngStart * 10, 10).Resize(10, 1), Target) > 1 Then
            Application.EnableEvents = False
            MsgBox "Duplicate Selection!", vbCritical, "Remove Data"
            Target.Value = ""
            Application.EnableEvents = True
          End If
        End If
      End If
    Case Else
  End Select

End Sub

You will need to update the number of rows which will be substracted in order to get the proper range to work with as in the first code the last row of a range would technically be included to the next range as it would return a different number ( rows 3 to 11 will rounddown to 0 while 12 will be 1 which is corrected in the last code so that 3 to 12 will be 0).

Holger

Thank you this worked!