Compile Error

[csenor]

I'm adding two VBA codes to a tab in a worksheet to either add a creation date or when something in the row is updated, add a modified date. I found the VBA code in another post and copied/pasted it into the VBA sheet. When I attempt to run it, I get a Compile Error: Ambiguous name detected. Can both sets of code exist in the same worksheet? I tried renaming the sub, but it didn't fix anything. I tried combining both codes, but it didn't produce a date in either column. Any help would be appreciated.


Code that works:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
   
    Application.ScreenUpdating = False
   
    For Each cell In Target
        Application.EnableEvents = False
        If cell.Row > 1 And cell.Column > 8 Then Cells(cell.Row, "AF") = Now()
        Application.EnableEvents = True
    Next cell
   
        Application.ScreenUpdating = True
   
End Sub

Code that doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
   
    Application.ScreenUpdating = False
   
    For Each cell In Target
        Application.EnableEvents = False
        If cell.Row > 1 And cell.Column > 8 Then Cells(cell.Row, "AF") = Now()
        Application.EnableEvents = True
    Next cell
   
   
For Each cell In Target
        Application.EnableEvents = False
        If cell.Row = 1 And cell.Column > 8 Then Cells(cell.Row, "AE") = Now()
        Application.EnableEvents = True
    Next cell

    Application.ScreenUpdating = True
   
End Sub

 

[Joe4]

You cannot have two procedures (Subs) in the same module with the same name.
You also cannot change the name of Event Procedure code. It MUST be saved exactly as it first appears, or else it will not be run automatically.

You can out two blocks of code in the same procedure.
Or, you can create multiple procedures that are NOT event procedure codes, and call those procedures from your event procedure code, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1
    Call Macro2
End Sub

 

[mikerickson]

You can have only one routine named
Worksheet_Change(ByVal Target As Range)

And if it is named anything else, it won't automatically run when the Change event fires.

Remove both of those routines and try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each cell In Target
       If cell.Row > 1 And cell.Column > 8 Then 
            Cells(cell.Row, "AF") = Now()
       ElseIf cell.Row = 1 And cell.Column > 8 Then 
            Cells(cell.Row, "AE") = Now()
        End IF
    Next cell
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
End Sub

 

[csenor]

1.

You can have only one routine named
Worksheet_Change(ByVal Target As Range)

And if it is named anything else, it won't automatically run when the Change event fires.

Remove both of those routines and try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each cell In Target
       If cell.Row > 1 And cell.Column > 8 Then
            Cells(cell.Row, "AF") = Now()
       ElseIf cell.Row = 1 And cell.Column > 8 Then
            Cells(cell.Row, "AE") = Now()
        End IF
    Next cell
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
End Sub

Am I putting this in the sheet itself or in a module for the workbook? I have several tabs in the workbook that I don't want to be affected.

 

[mikerickson]

That code would go in the code module for the sheet where you want it to work.

The "code that works", wherever that code was would be the place to put the code I posted (after you delete the OP routine).

 

[csenor]

Thank you, sir. It works great.