Ambiguous name error

[Patriot2879]

Hi I have the code below but I get an ambiguous name error come up please can you help.
the error comes on the line below

Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_Change(ByVal Target As Range)
    'This code goes in Book13, amend for book 14
    
    
    'this sub requires both workbooks to be open
    Dim wb As Workbook
    Dim spath As String, sFileName As String
    Dim bOpen As Boolean 'remember if workbook was open or not
    
    spath = "C:\Users\s21375\Desktop\"
    
    ' *** This line needs changing for Book 14
    sFileName = "Test1.xlsm"
    
    On Error Resume Next
    Set wb = Workbooks(sFileName)
    If wb Is Nothing Then
        Set wb = Workbooks.Open(spath & sFileName)
        If wb Is Nothing Then
            MsgBox "File can not be opened", vbCritical
            Exit Sub
        End If
    Else
        bOpen = True
    End If
    On Error GoTo 0
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    wb.Sheets(Me.Name).Range(Target.Address) = Target
    If bOpen = False Then
        wb.Save

    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    
    If Not Intersect(Target, Me.Range("A2:R18")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

 

[Patriot2879]

Re: Ambigious name error please help

Hi thanks for your help. Will this work okay as joe4 advised thst I have checks that exit the code ("Exit Sub" statements) and Error Handling code in both procecures. And i would need to combine those both into a single error handling routine.

 

[ZVI]

Re: Ambigious name error please help

Hi thanks for your help. Will this work okay as joe4 advised thst I have checks that exit the code ("Exit Sub" statements) and Error Handling code in both procedures. And i would need to combine those both into a single error handling routine.

This will work ok for the code of #4 because Exit Sub is in the Worksheet_Change2 part only.
For common cases use a global Boolean variable outside the subs for flagging errors to stop continue code of other subs in the main Sub Worksheet_Change()

 

[ZVI]

Re: Ambigious name error please help

...For the code of post #17 just add one more part - Worksheet_Change3

Seems the 3d part of the code in #17 is a duplicate of the 2nd part, thus the Worksheet_Change3 is not required

 

[Patriot2879]

Re: Ambigious name error please help

Ok thank you. I just put the code in both workbooks and change the filenames?

 

[ZVI]

Re: Ambigious name error please help

I just put the code in both workbooks and change the filenames?

Seems yes, but just try what you are asking for and test it.
My suggestion was only on how to exclude Ambiguous name error in the code of sheet's module.
Regards

 

[Patriot2879]

Re: Ambigious name error please help

Hi I have amended the code please see below but I get an error on the last line 'End Sub' for some reason. The Error is 'Block If witou End If'. hope you can advise

Private Sub Worksheet_Change(ByVal Target As Range)
Call Worksheet_Change1(Target)
 Call Worksheet_Change2(Target)
End Sub
Private Sub Worksheet_Change1(ByVal Target As Range)
Dim i As Long, j As Long, errorFound
On Error GoTo errorFound
If Target.Address <> "$AC$6" Then Exit Sub
Application.EnableEvents = False
    ' Determine Row # and Column #
    i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
    j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
    
    ' Adjust the Intersection cell Value by substracting Input in AC6
    Cells(i, j).Value = Cells(i, j).Value - Target
    
    ' Clear ONLY Target cell
    Target.ClearContents
                
errorFound:
Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_Change2(ByVal Target As Range)
    'This code goes in Book13, amend for book 14
    
    
    'this sub requires both workbooks to be open
    Dim wb As Workbook
    Dim spath As String, sFileName As String
    Dim bOpen As Boolean 'remember if workbook was open or not
    
    spath = "C:\Users\s21375\Desktop\"
    
    ' *** This line needs changing for Book 14
    sFileName = "Test1.xlsm"
    
    On Error Resume Next
    Set wb = Workbooks(sFileName)
    If wb Is Nothing Then
        Set wb = Workbooks.Open(spath & sFileName)
        If wb Is Nothing Then
            MsgBox "File can not be opened", vbCritical
            Exit Sub
        End If
    Else
        bOpen = True
    End If
    On Error GoTo 0
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    wb.Sheets(Me.Name).Range(Target.Address) = Target
    If bOpen = False Then
        wb.Save

    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    
    If Not Intersect(Target, Me.Range("B3:R23")) Is Nothing Then
        ThisWorkbook.Save
        
    End Sub

 

[Patriot2879]

Re: Ambigious name error please help

hope you can help with this error, I am confused now as this code worked before

 

[ZVI]

Re: Ambigious name error please help

End If is missing before End Sub - see post #4.
Here is the fixing:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
  Call Worksheet_Change1(Target)
  Call Worksheet_Change2(Target)
End Sub
 
Private Sub Worksheet_Change1(ByVal Target As Range)
  Dim i As Long, j As Long, errorFound
  On Error GoTo errorFound
  If Target.Address <> "$AC$6" Then Exit Sub
  Application.EnableEvents = False
  ' Determine Row # and Column #
  i = Application.Match(Range("AC3"), Range("A1:A18"), 0)
  j = Application.Match(Range("AC4"), Range("A2:R2"), 0)
 
  ' Adjust the Intersection cell Value by substracting Input in AC6
  Cells(i, j).Value = Cells(i, j).Value - Target
 
  ' Clear ONLY Target cell
  Target.ClearContents
 
errorFound:
  Application.EnableEvents = True
End Sub
 
Private Sub Worksheet_Change2(ByVal Target As Range)
'This code goes in Book13, amend for book 14
'this sub requires both workbooks to be open
 
  Dim wb As Workbook
  Dim spath As String, sFileName As String
  Dim bOpen As Boolean   'remember if workbook was open or not
 
  spath = "C:\Users\s21375\Desktop\"
 
  ' *** This line needs changing for Book 14
  sFileName = "Test1.xlsm"
 
  On Error Resume Next
  Set wb = Workbooks(sFileName)
  If wb Is Nothing Then
    Set wb = Workbooks.Open(spath & sFileName)
    If wb Is Nothing Then
      MsgBox "File can not be opened", vbCritical
      Exit Sub
    End If
  Else
    bOpen = True
  End If
  On Error GoTo 0
 
  Application.EnableEvents = False
  Application.ScreenUpdating = False
 
  wb.Sheets(Me.Name).Range(Target.Address) = Target
  If bOpen = False Then
    wb.Save
  End If
  Application.EnableEvents = True
  Application.ScreenUpdating = True
 
 
  If Not Intersect(Target, Me.Range("A2:R18")) Is Nothing Then
    ThisWorkbook.Save
  End If  ' <-- This was missing
 
End Sub

 

[Patriot2879]

Re: Ambigious name error please help

Hi thank you for this, when I update the cell n AC6 with a number which gets deducted to the correct cell when it cross references the area with date it updates correctly in test2 worksheet. It doesn't update in test1 worksheet please can you help with this?

 

[ZVI]

Re: Ambigious name error please help

Hi thank you for this, when I update the cell n AC6 with a number which gets deducted to the correct cell when it cross references the area with date it updates correctly in test2 worksheet. It doesn't update in test1 worksheet please can you help with this?

The code of Worksheet_Change runs only in the sheet with that code and only after values of cells are changed manually (not by formulas).