Ambiguous Name Detected: Worksheet_Change error

K

kiwikiki718

Board Regular
Joined
Apr 7, 2017
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
You cannot have 2 procedures with the same name if they have the same scope. Two procedures in the same module (e.g. sheet module) certainly have the same scope.
Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.
 
Upvote 0
kiwikiki718 said:
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
Click to expand...
VBA 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

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
 
Upvote 0
Micron said:
You cannot have 2 procedures with the same name if they have the same scope. Two procedures in the same module (e.g. sheet module) certainly have the same scope.
Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.
Click to expand...
Thank you I reposted the code. How can I fix the issue, I tried removing one of the worksheet change and still was unable to run the code. both events runs correctly separately. I was just trying to figure out how to combine them.
 
Upvote 0
So the 3rd one is included in the first code post that shows the same event 2x? Or you really have 3? It's confusing.
kiwikiki718 said:
I tried removing one of the worksheet change and still was unable to run the code
Click to expand...
That means what, you got the same error? If so, that suggests you have more of them somewhere.
 
Upvote 0
VBA 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("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


sorry here is the 2 subs I was trying to combine into one.
 
Upvote 0
Hi kiwikiki718,

maybe this can help (please mind this to be the only procedure with that name behind the sheet - you should mark the other ones and comment them out using the Toolbar):

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngStart 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 - 2) / 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 - 86) / 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

Please look at the correct reference for the CountIf: first is the area which is checked, second the value or argument to check.

Ciao
Holger
 
Upvote 0
HaHoBe said:
Hi kiwikiki718,

maybe this can help (please mind this to be the only procedure with that name behind the sheet - you should mark the other ones and comment them out using the Toolbar):

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lngStart 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 - 2) / 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 - 86) / 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

Please look at the correct reference for the CountIf: first is the area which is checked, second the value or argument to check.

Ciao
Holger
Click to expand...
Thank you. just so I am clear. both codes I indicated works good when ran on its own.

the first code is checking the value in cell A/B and defaulting the value to 0 if it is blank
VBA 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 If







The second code is checking a range of cells that contain a dropdown list to ensure duplicates are not being selected.
VBA Code: 
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
 
Upvote 0
Hi kiwikiki718,

why don't you make a copy of the workbook, in that copy delete the codes behind the sheet in question and insert the procedure posted here and test?

In the given sheet you only need the code to ruun if any single cell in Columns A, B, and J is changed. The first segment regards the columns A and B and only starts if any match between the target and the range in question is found.

As there is a break in the numbers in Column J this has to be split into bits. For any single half the range to check (which covers 10 cells) is build and compared if any match is found. If so the entry is substituted by "" or vbNullString.

I suppress the event which would get fired when any cell will be changed but put events on again directly after.

In short: I can´t tell you if the code is working if you don't test it - I did my best to spot anything but as usual I'm not sure everything is covered.

Holger
 
Upvote 0
HaHoBe said:
Hi kiwikiki718,

why don't you make a copy of the workbook, in that copy delete the codes behind the sheet in question and insert the procedure posted here and test?

In the given sheet you only need the code to ruun if any single cell in Columns A, B, and J is changed. The first segment regards the columns A and B and only starts if any match between the target and the range in question is found.

As there is a break in the numbers in Column J this has to be split into bits. For any single half the range to check (which covers 10 cells) is build and compared if any match is found. If so the entry is substituted by "" or vbNullString.

I suppress the event which would get fired when any cell will be changed but put events on again directly after.

In short: I can´t tell you if the code is working if you don't test it - I did my best to spot anything but as usual I'm not sure everything is covered.

Holger
Click to expand...
Thanks I will give it a try and follow up.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
duplicate entry restriction VBA Code causing issue in other portion
Replies
9
Views
117
Alex Blakenburg
A
R
  • Question Question
color cell in red, when entering empty time value
Replies
2
Views
176
robertvdb
R
T
  • Question Question
Remove duplicates
Replies
2
Views
241
tiredofit
T
SaraWitch
  • Solved
Run Multiple Private Sub Worksheet_Change(ByVal Target As Range)
Replies
5
Views
815
Joe4
Joe4
K
  • Locked
  • Question Question
Vba code for User history log which is giving me a run-time error "13" mismatch
Replies
2
Views
168
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top