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

 

[Joe4]

Re: Ambigious name error please help

Do you have two procedures with the name "Private Sub Worksheet_Change" in that VBA module?
That is not allowed. You cannot repeat procedure names within a single module.
You would need to combine them into a single procedure.

 

[Patriot2879]

Re: Ambigious name error please help

ok how would I do that please?

 

[Patriot2879]

Re: Ambigious name error please help

This is the other code with same name, how would I change?

Private Sub Worksheet_Change(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_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

 

[Joe4]

Re: Ambigious name error please help

This is the other code with same name, how would I change?

Typically, one would just create two blocks of code under the same procedure. However, yours is going to be a little more tricky because you have checks that exit the code ("Exit Sub" statements) and Error Handling code in both procecures. You would need to combine those both into a single error handling routine.

You could also write other sub procedures with other names (that are not event procedures), and call those the Worksjeet_Change event procedure, if you feel it easier to keep the code separate.
See: https://docs.microsoft.com/en-us/of...g-started/calling-sub-and-function-procedures

 

[Patriot2879]

Re: Ambigious name error please help

hi thanks for the information that all sounds quite complicated

 

[James006]

Re: Ambigious name error please help

Hi,

Now ... easier to understand your message

https://www.mrexcel.com/forum/excel...d-cell-deduct-total-how-do-3.html#post5333134

Excel crashes are not due to the event macro ... but to your ' Ambigious Name ' error ...

 

[Patriot2879]

Re: Ambigious name error please help

Hi please can you help me on how to combine the 2 codes together please.

 

[Joe4]

Re: Ambigious name error please help

I really do not like trying to look at people's code and attempt to reverse engineer it and "guess" what they are trying to do (especially when I don't even have the data to see how it is interacting with it).

In order to help you, I would need the following:
1. To see what the data looks like
2. A plain English description of all that you would like it to do

I see that James006 chimed in on this, and it looks like he was working with you on another thread. Is that related to this?

 

[Patriot2879]

Re: Ambigious name error please help

Hi yes james006 has been helping me. Thank you. Would it be best if I send a link to the file tomorrow morning?