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

 

[ZVI]

Re: Ambigious name error please help

hi the line goes red straight away for the code below, so not sure if I did it correctly. Yes I have permission for both folders

If LCase(sThisFullName) = LCase "C:\Users\s21375\Desktop\Test1\test11.xlsm" Then

Do not change any code lines, only define properly the constants FullName1 and FullName2 in the top of the code, like this:

  '--> User settings, change to suit
  Const FullName1 = "G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\test\test22.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\test2\test11.xlsm"
  '<-- End of the settings

 

[ZVI]

Re: Ambigious name error please help

Use this code for your folders:

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\test\test22.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\test2\test11.xlsm"
  '<-- End of the settings
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sThisFullName As String, sFileName As String
 
  On Error GoTo exit_
  If Target.Address <> "$AC$6" Then Exit Sub
 
  ' Save Target.Value
  TargetValue = Target.Value
 
  ' Disable events handling
  Application.EnableEvents = False
  Application.Calculation = xlCalculationAutomatic
 
  ' 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 - TargetValue
 
  ' Clear ONLY Target cell
  Target.ClearContents
  Target.Select
 
  ' Update A2 current region in another workbook in the same folder
  sThisFullName = ThisWorkbook.FullName
  If LCase(sThisFullName) = LCase(FullName1) Then
    sFileName = FullName2
  Else
    sFileName = FullName1
  End If
  Application.ScreenUpdating = False
  a() = Me.Range("A2").CurrentRegion.Value
  With Workbooks.Open(sFileName, UpdateLinks:=False)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
    .Save
    '.Close
  End With
 
exit_:
 
  ' Restore events handling and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

 

[Patriot2879]

Re: Ambigious name error please help

Hiya okay thank you I shall try that I think I was changing the wrong lines wasn't I? I was changing the lines below.

 sThisFullName = ThisWorkbook.FullName
  [COLOR=darkblue]If[/COLOR] LCase(sThisFullName) = LCase(FullName1) [COLOR=darkblue]Then[/COLOR]
    sFileName = FullName2
  [COLOR=darkblue]Else[/COLOR]
    sFileName = FullName1

 

[ZVI]

Re: Ambigious name error please help

Hiya okay thank you I shall try that I think I was changing the wrong lines wasn't I? I was changing the lines below.

 sThisFullName = ThisWorkbook.FullName
  [COLOR=darkblue]If[/COLOR] LCase(sThisFullName) = LCase(FullName1) [COLOR=darkblue]Then[/COLOR]
    sFileName = FullName2
  [COLOR=darkblue]Else[/COLOR]
    sFileName = FullName1

Yes Use the code in my previous post

 

[Patriot2879]

Re: Ambigious name error please help

Hi I have updated with the code I have used test22 but it hasn't updated test11 I have checked the source where they are saved and both is correct

 

[ZVI]

Re: Ambigious name error please help

Code in post #62 works correctly for workbooks test1.xlsm and test2.xlsm stored in desktop’s folders test1 and test2, and it should work properly for any folders and workbook names. Check if the code is exactly the same in both workbooks including values of the constants FullName1 and FullName2

 

[Patriot2879]

Re: Ambigious name error please help

Hi i have checked both codes for test1 and test11 they both look the same I cant see anything different apart from the location of test11. please both codes below.
test1

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sPath As String, sFileName As String
 
  On Error GoTo exit_
  If Target.Address <> "$AC$6" Then Exit Sub
 
  ' Save Target.Value
  TargetValue = Target.Value
 
  ' Disable events handling
  Application.EnableEvents = False
  Application.Calculation = xlCalculationAutomatic
 
  ' 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 - TargetValue
 
  ' Clear ONLY Target cell
  Target.ClearContents
 
  ' Update A2 current region in another workbook in the same folder
  sPath = ThisWorkbook.Path & "\"
  If LCase(ThisWorkbook.Name) = "test1.xlsm" Then
    sFileName = sPath & "test2.xlsm"
  Else
    sFileName = sPath & "test1.xlsm"
  End If
  Application.ScreenUpdating = False
  a() = Me.Range("A2").CurrentRegion.Value
  With Workbooks.Open(sFileName, UpdateLinks:=False)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
    .Save
    '.Close
  End With
  Me.Parent.Activate
 
exit_:
 
  ' Restore events handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

test11

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\test\test22.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\test2\test11.xlsm"
  '<-- End of the settings
 
  Dim a() As Variant, i As Long, j As Long, TargetValue
  Dim sThisFullName As String, sFileName As String
 
  On Error GoTo exit_
  If Target.Address <> "$AC$6" Then Exit Sub
 
  ' Save Target.Value
  TargetValue = Target.Value
 
  ' Disable events handling
  Application.EnableEvents = False
  Application.Calculation = xlCalculationAutomatic
 
  ' 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 - TargetValue
 
  ' Clear ONLY Target cell
  Target.ClearContents
  Target.Select
 
  ' Update A2 current region in another workbook in the same folder
  sThisFullName = ThisWorkbook.FullName
  If LCase(sThisFullName) = LCase(FullName1) Then
    sFileName = FullName2
  Else
    sFileName = FullName1
  End If
  Application.ScreenUpdating = False
  a() = Me.Range("A2").CurrentRegion.Value
  With Workbooks.Open(sFileName, UpdateLinks:=False)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
    .Save
    '.Close
  End With
 
exit_:
 
  ' Restore events handling and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub

This line below is the only differene

  ' Update A2 current region in another workbook in the same folder
  sThisFullName = ThisWorkbook.FullName
  If LCase(sThisFullName) = LCase(FullName1) Then
    sFileName = FullName2
  Else
    sFileName = FullName1
  End If
  Application.ScreenUpdating = False
  a() = Me.Range("A2").CurrentRegion.Value
  With Workbooks.Open(sFileName, UpdateLinks:=False)
    .Sheets(Me.Name).Range("A2").CurrentRegion.Resize(UBound(a), UBound(a, 2)).Value = a()
    .Save
    '.Close
  End With

 

[ZVI]

Re: Ambigious name error please help

Hi I have updated with the code I have used test22 but it hasn't updated test11 I have checked the source where they are saved and both is correct

Hi i have checked both codes for test1 and test11 they both look the same I cant see anything different apart from the location of test11

Hi,
Not sure why you have compared test1 with test11, do you synchronize more than 2 workbooks?
Code in both used workbooks should be the same.
If you are using test11.xlsm with test22.xlsm, then copy code of test11.xlsm into test22.xlsm

 

[Patriot2879]

Re: Ambigious name error please help

hiya I have tried that, but still didn't work, shall I try a different folder?

 

[ZVI]

Re: Ambigious name error please help

If test22.xlsm is stored in "G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\test\test22.xlsm"
and test11.xlsm is in "C:\Users\s21375\Desktop\test2\test11.xlsm" then use code of #72 for both workbooks, it should work.
If it does not work please answer the questions:

1. Does error message appear?
If yes then what is in error message?

else (if not error):

2. Does test11 updates test22?

3. Does test22 updates test11?