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

great, thank you again for all your help, speak to you soon :)
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Re: Ambigious name error please help

Below is the code version for workbooks stored in different folders declared by constants FullName1 and FullName2.
Rich (BB code): 
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.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 handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub
Regards
 
Last edited:
Upvote 0
Re: Ambigious name error please help

Great thanks I will give it a go in the morning:)
 
Upvote 0
Re: Ambigious name error please help

Hi ZVI good morning, I think I have gone a bit wrong on putting the path in as it has all gone red, I have changed test1 to test11 and test2 to test22 plase see code belowwhere I have added th paths,please can you advise where I went wrong.
Code: 
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.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 C:\Users\s21375\Desktop\test2\test11.xlsm Then
    sFileName = G:\TEAM\ES_Mtr_Tech_Ops\Customer Management Centre\Utilisation\Complex\test\test22.xlsm
  Else
    sFileName = C:\Users\s21375\Desktop\test2\test11.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
 
exit_:
 
  ' Restore events handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub
 
Upvote 0
Re: Ambigious name error please help

Hi,

Store both workbooks anywhere you want.

Activate in Excel one of them, in VBE Immediate window type: ?ActiveWorkbook.FullName
and then press Return key. Copy a full name appeared in Immediate window and paste it in the code as value of one of the constants, for example like this:
Const FullName1 = "C:\Users\s21375\Desktop\Test1\test11.xlsm"
In Red is what is appeared in the Immediate window

Do the same with another workbook, for example like this:
Const FullName2 = "C:\Users\s21375\Desktop\Test2\test12.xlsm"

Hope this will help.
Regards
 
Last edited:
Upvote 0
Re: Ambigious name error please help

hiya still abit confused I have changed to the following but still getting errors.
Code: 
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.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 "C:\Users\s21375\Desktop\Test1\test11.xlsm" Then
    sFileName = Const FullName2 = "C:\Users\s21375\Desktop\Test2\test12.xlsm"
  Else
    Const FullName1 = "C:\Users\s21375\Desktop\Test1\test11.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
 
exit_:
 
  ' Restore events handlong and screen updating
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  ThisWorkbook.Activate
 
  ' Inform about error
  If Err Then MsgBox Err.Description, vbCritical, "Error!"
 
End Sub
 
Upvote 0
Re: Ambigious name error please help

Hi Sorry this is where the 2 files are saved on code above I put in same place. Test11 was previously test1 and test22 was previously test2

G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\test\test22.xlsm
C:\Users\s21375\Desktop\test2\test11.xlsm
 
Upvote 0
Re: Ambigious name error please help

Patriot2879 said:
hiya still abit confused I have changed to the following but still getting errors.
Code: 
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
  '--> User settings, change to suit
  Const FullName1 = "C:\Users\s21375\Desktop\Test1\test1.xlsm"
  Const FullName2 = "C:\Users\s21375\Desktop\Test2\test2.xlsm"
  '<-- End of the settings

  '...
 
End Sub
Click to expand...
What is in the error message?
In the previous post test11 and test12 were mentioned but it is not reflected in the code/
Well, for the testing use test1.xlsm in C:\Users\s21375\Desktop\Test1 folder and test2.xlsm in C:\Users\s21375\Desktop\Test2 folder., does it work?
 
Upvote 0
Re: Ambigious name error please help

Patriot2879 said:
Hi Sorry this is where the 2 files are saved on code above I put in same place. Test11 was previously test1 and test22 was previously test2

G:\TEAM\ES_Mtr_Tech_Ops\Customer ManagementCentre\Utilisation\Complex\test\test22.xlsm
C:\Users\s21375\Desktop\test2\test11.xlsm
Click to expand...
Do you have permission to write in both folders?
What is in the error message?
 
Upvote 0
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 :)
Code: 
If LCase(sThisFullName) = LCase "C:\Users\s21375\Desktop\Test1\test11.xlsm" Then
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Emailing 1 sheet
Replies
6
Views
255
Micron
M
B
  • Solved
Error on method intersect
Replies
2
Views
95
Beneindias
B
O
  • Question Question
Creating workbooks for a team of staff
Replies
1
Views
200
HighAndWilder
H
SaraWitch
  • Solved
Run Multiple Private Sub Worksheet_Change(ByVal Target As Range)
Replies
5
Views
768
Joe4
Joe4
welshraz
  • Question Question
VBA - Restrict Code to One Column for Multi-choice Dropdown
Replies
2
Views
81
welshraz
welshraz

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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