+ Reply to Thread
Results 1 to 16 of 16

Ambiguous name detected vba excel

  1. #1
    Registered User
    Join Date
    01-03-2013
    Location
    Chennai,India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Ambiguous name detected vba excel

    Hi ppl,

    Im getting Ambiguous name detected vba excel : Worksheet_Change for the below code.
    pl help

    Please Login or Register  to view this content.
    Moderator's Edit: Use code tags when posting code. To do so in future, select the code and click on the # icon at the top of your post window.
    Last edited by priyaberk; 01-03-2013 at 06:11 AM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Ambiguous name detected vba excel

    thread moved.

    please add code tags to your post before we proceed
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Ambiguous name detected vba excel

    The name "Worksheet_Change" is also the name used for private routines on worksheet level. I would suggest to change the name of your public routine
    If you like my contribution click the star icon!

  4. #4
    Registered User
    Join Date
    01-03-2013
    Location
    Chennai,India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Ambiguous name detected vba excel

    can u pl suggest me how to change the public routine as I am very new to macro coding?

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

    Re: Ambiguous name detected vba excel

    What other code to you have?

    Do you have any more subs called Worksheet_Change?
    If posting code please use code tags, see here.

  6. #6
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Ambiguous name detected vba excel

    The correction you need to make is dependent on where the routine is placed (in a worksheet or generic code module).

    If the routine is placed in a worksheet module the name is dictated by Excel VBA conventions and may not be changed, otherwise the routine will not be triggered when the associated event occurs. In that case, the only thing you need to do is change the text from "Public" into "Private" on the first line. You may need to do this for every occurrence of this routine name as the error mentioned by you suggests there are more routines declared this way.

    If the code is placed in a generic module it is not an event based routine and the name can be changed into anything you want

    for example:

    Please Login or Register  to view this content.
    If you have a hard time understanding the above, you may also post your workbook and then I will make the changes for you.

  7. #7
    Registered User
    Join Date
    01-03-2013
    Location
    Chennai,India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Ambiguous name detected vba excel

    @Norie
    there is only one sub is named Worksheet_Change

    @Olaf
    It is a event based scenario.Based on the value of x cell in sheet 1 then the corresponding validation will happen in sheet 2.

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

    Re: Ambiguous name detected vba excel

    If there's only one sub with that name you shouldn't get the error.

    Where/when do you get the error?

    Can you attach a workbook?

  9. #9
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Ambiguous name detected vba excel

    @Priya,

    In that case change the Public designation into a Private one for all occurrences of the Worksheet_Change event

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

    Re: Ambiguous name detected vba excel

    Olaf

    You can declare the Worksheet_Change event as Public or Private.

  11. #11
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Ambiguous name detected vba excel

    @Norie,

    I know but not if you have more than one of those. The error message mentioned by Priya tends to indicate that there is more than one.

  12. #12
    Registered User
    Join Date
    01-03-2013
    Location
    Chennai,India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Ambiguous name detected vba excel

    I have declared the ub as Public already.

  13. #13
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: Ambiguous name detected vba excel

    Quote Originally Posted by priyaberk View Post
    I have declared the ub as Public already.
    Priya - I think it is best if you post the workbook

  14. #14
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Ambiguous name detected vba excel

    scope is irrelevant if the code is in a worksheet module. you would only get an ambiguous name error if you have two change events in the same sheet

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

    Re: Ambiguous name detected vba excel

    I've asked the OP if there are any more Worksheet_Change subs and they said there was only the one.

  16. #16
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Ambiguous name detected vba excel

    based on the information provided to date the op would seem to be mistaken ;-) will wait for the workbook with interest

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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