Ambiguous name error

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
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
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Code: 
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
 
Sort by date Sort by votes
Re: Ambigious name error please help

Patriot2879 said:
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 :)
Code: 
If LCase(sThisFullName) = LCase "C:\Users\s21375\Desktop\Test1\test11.xlsm" Then
Click to expand...
Do not change any code lines, only define properly the constants FullName1 and FullName2 in the top of the code, like this:
Rich (BB code): 
  '--> 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
 
Last edited:
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Re: Ambigious name error please help

Use this code for your folders:
Rich (BB code): 
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
 
Last edited:
Upvote 0
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.
Code: 
 sThisFullName = ThisWorkbook.FullName
  [COLOR=darkblue]If[/COLOR] LCase(sThisFullName) = LCase(FullName1) [COLOR=darkblue]Then[/COLOR]
    sFileName = FullName2
  [COLOR=darkblue]Else[/COLOR]
    sFileName = FullName1
 
Upvote 0
Re: Ambigious name error please help

Patriot2879 said:
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.
Code: 
 sThisFullName = ThisWorkbook.FullName
  [COLOR=darkblue]If[/COLOR] LCase(sThisFullName) = LCase(FullName1) [COLOR=darkblue]Then[/COLOR]
    sFileName = FullName2
  [COLOR=darkblue]Else[/COLOR]
    sFileName = FullName1
Click to expand...
Yes :) Use the code in my previous post
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
Code: 
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
Code: 
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
Code: 
  ' 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
 
Last edited:
Upvote 0
Re: Ambigious name error please help

Patriot2879 said:
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
Click to expand...
Patriot2879 said:
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
Click to expand...
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
 
Last edited:
Upvote 0
Re: Ambigious name error please help

hiya I have tried that, but still didn't work, shall I try a different folder?
 
Upvote 0
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?

 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Emailing 1 sheet
Replies
6
Views
249
Micron
M
B
  • Solved
Error on method intersect
Replies
2
Views
64
Beneindias
B
O
  • Question Question
Creating workbooks for a team of staff
Replies
1
Views
186
HighAndWilder
H
SaraWitch
  • Solved
Run Multiple Private Sub Worksheet_Change(ByVal Target As Range)
Replies
5
Views
700
Joe4
Joe4
M
  • Solved
Trying to apply 2 Worksheet Changes
Replies
1
Views
131
Joe4
Joe4

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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