+ Reply to Thread
Results 1 to 8 of 8

Compile Error Ambiguous name detected worksheet_change

Hybrid View

  1. #1
    Registered User
    Join Date
    01-20-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Compile Error Ambiguous name detected worksheet_change

    So im using this code to create time stamps

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)'Check is column J (10)
    If Not Target.Column = 10 Then Exit Sub
    'Check is single cell entry
    If Not Target.Cells.Count = 1 Then Exit Sub
    'otherwise datestamp K in same row
    Target.Offset(0, 1) = Now
    End Sub
    However I want to repeat this with column N and O

    I tried to create my own code in notepad, This is what i came up with

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Check is column N (10)
    If Not Target.Column = 10 Then Exit Sub
    'Check is single cell entry
    If Not Target.Cells.Count = 1 Then Exit Sub
    'otherwise datestamp O in same row
    Target.Offset(0, 1) = Now
    End Sub

    However its coming up with "Compile Error Ambiguous name detected worksheet_change"

    what am i doing wrong ?

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Compile Error Ambiguous name detected worksheet_change

    Do you have two identically named (worksheet_change) subroutines in the same worksheet module?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Compile Error Ambiguous name detected worksheet_change

    Try this.
    Private Sub Worksheet_Change(ByVal Target As Range)'Check is column J (10)
        If Intersect(Range("J:J,N:N"), Target) Is Nothing Then Exit Sub
        'Check is single cell entry
        If Not Target.Cells.Count = 1 Then Exit Sub
        'otherwise datestamp K in same row
        Target.Offset(0, 1) = Now
    
    End Sub
    If posting code please use code tags, see here.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,650

    Re: Compile Error Ambiguous name detected worksheet_change

    HAve you replaced or added new procedure?

    Old citation: "There can be only one" ;-) applies here - in one sheet module you cannot have two Worksheet_Change (ora ny other two with the same name) procedures.


    PS. Columns N and O are rather 14 and 15th, but I think the problem is as described above - two procedures with the same name in one module.
    Last edited by Kaper; 01-20-2014 at 08:55 AM.

  5. #5
    Registered User
    Join Date
    01-20-2014
    Location
    Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Compile Error Ambiguous name detected worksheet_change

    Ok so I ended up doing this.

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Check is single cell entry
    If Not Target.Cells.Count = 1 Then Exit Sub
    'Check is column J (10) or N (14) or R (18) or V (22) or Z (26) or AB (30) or AD (34) or AH (38) or AL (42) and if yes then  datestamp cell to right
    If Target.Column = 10 Or Target.Column = 14 Or Target.Column = 18 Or Target.Column = 22 Or Target.Column = 26 Or Target.Column = 30 Or Target.Column = 34 Or Target.Column = 38 Or Target.Column = 42 Then Target.Offset(0, 1) = Now
    End Sub
    Thank you everyone for your suggestions

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,650

    Re: Compile Error Ambiguous name detected worksheet_change

    How about possible extensions for further columns?
    They are spaced quite regular in your sheet, so may be the following is worth considering:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tc As Long
    If Not Target.Cells.Count = 1 Then Exit Sub
    tc = Target.Column
    If tc >= 10 And tc Mod 4 = 2 And tc <= 42 Then Target.Offset(0, 1) = Now
    End Sub

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Compile Error Ambiguous name detected worksheet_change

    The code I posted will work for columns J and N, it can be adapted if you need it to work for other columns.

    Something like this perhaps.
    Private Sub Worksheet_Change(ByVal Target As Range) 'Check is column J (10)
    
        If Target.Column < 10 Or Target.Column > 38 Or Target.Column Mod 4 <> 2 Then Exit Sub
    
        If Not Target.Cells.Count = 1 Then Exit Sub
    
        Target.Offset(0, 1) = Now
    
    End Sub
    By the way, I've used 38 in the code as that's the column number of column AL.

  8. #8
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Compile Error Ambiguous name detected worksheet_change

    Another potentially useful approach is to select the case of the column number:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Cells.Count = 1 Then Exit Sub
    Select Case Target.Column
        Case 10, 14, 18, 22, 26, 30, 34, 38, 42:    Target.Offset(0, 1) = Now
    End Select
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Compile Error: Ambiguous name detected
    By kieranoduill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2013, 05:31 PM
  2. [SOLVED] compile error ambiguous name detected worksheet_change
    By MAttTaylor87 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-22-2013, 07:07 AM
  3. [SOLVED] compile error ambiguous name detected!?
    By Margate in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2013, 08:49 PM
  4. [SOLVED] Compile error: Ambiguous name detected: Worksheet_Change
    By ericwilder in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 01-30-2013, 12:49 PM
  5. compile error: Ambiguous name detected :worksheet_change
    By ckz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2012, 12:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1