Closed Thread
Results 1 to 12 of 12

[SOLVED] compile error ambiguous name detected

  1. #1
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    [SOLVED] compile error ambiguous name detected

    Hello, Im using a script that allows me to autocomplete a data validation list using a combo box and the code shown below. However I am getting a compile error ambigous name detected and the following line is highlighted
    "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"

    I know its because this is used twice but I am not sure what to do in order to fix this. I would appreciate some help on this.

    Thank you.

    Please Login or Register  to view this content.
    Last edited by truman40; 02-16-2010 at 05:22 PM. Reason: Changed quote tags to code tags

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: compile error ambigous name detected

    Good afternoon truman40
    Quote Originally Posted by truman40 View Post
    I am getting a compile error ambigous name detected and the following line is highlighted
    "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"
    The ambiguous name error usually means that you have two subs within a module of the same name. You are trying to call two Worksheet_SelectionChange events, which is just not allowed.

    Anything you want to happen when this event is fired has to go into one sub. Use some kind of structured decision within here to determine exactly what is to happen, but keep it in one sub.

    HTH

    DominicB

    Edit : I have highlighted the erroneous lines in your post above, just to save having to list all your code again.
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: compile error ambigous name detected

    Thank you for your help Dominic but I am very new to this and didnt quite understand what you meant. How exactly would I do this "Use some kind of structured decision " to fix this problem?

  4. #4
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: compile error ambigous name detected

    I have included an example file to explain what I am trying to do. (Sorry cant upload at it present because I am at work and connected through a server)
    I have followed a procedure shown here http://www.contextures.com/xlDataVal14.html

    Which allows me to use autocomplete, change font size etc on a validated list by using a combo box. This works on the first two of my validated lists in cells C10 and E14 however will not work on my last validated list at G14. My guess is that because the list at G14 is sourced by =indirect(E14) that this is my problem.
    I am not sure of what my options are here. The lists at G14 are thousands of suburbs long so I need to have autocomplete working.

    Thanks very much for any help offered, much appreciated.

    Troy.

  5. #5
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: compile error ambigous name detected

    Here is the attached file here but I have had to remove the VBA code to keep the file under 1mb. But I think you experts will get the jist of it.
    basically my code to allow autocomplete only works with the first two validated lists but not the last. Im guessing because it uses the validation =indirect(E14).

    Can somebody pleaese help me out with this one?

    Thanks
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: compile error ambigous name detected

    Someone????? Anyone?????? Please???????

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: compile error ambiguous name detected

    If the problem is with code and you strip the code from the workbook what use is the workbook, regardless of size?

    Try stripping back the data and leave the code intact so we can see the error.
    Also try zipping the file is it is still that big.
    Cheers
    Andy
    www.andypope.info

  8. #8
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: compile error ambiguous name detected

    Andy,

    I stripped the code out because it was posted in my first post so I thought it would be easier to send it without the code and then just view it from my first post. However thanks for the tip about zipping it as I have done that and it is now small enough to post. Here it is again with code included.

    Thanks for your help.
    Attached Files Attached Files

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: compile error ambiguous name detected

    The attached does not have the error because you removed the double code.
    The sheet can only have 1 event. Within the event you need to determine what it is you need to do.

    But if you are reacting to comboboxes why not but the code in the combobox events.

    What exactly are you trying to do?

  10. #10
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: compile error ambiguous name detected

    Andy,
    I didnt actually need the double code so I removed it but the problem still occurs.
    Im trying to use the code and combo boxes so I can use the autocomplete feature on my validated lists. My actual validated lists are very long with thousands of lines. If I was to just use a standard validated list my user would have to scroll through the entire thing to find what they are looking for. But with the vb code and a combo box they can start to type say EXC in C10 and the autocomplete feature takes them to EXCAVATOR 301.5. The script works on my first two validated lists at C10 and E14 (although it doesnt need to work at E14 because the list there is quite small anyway.) but will not work at G14 which has a validated list that is over 2000 suburbs.
    Im guessing this is because G14 gets its validation source from =INDIRECT(E14).

    So is there a way that I can get the script to duplicate at G14 what it does at C14 and E14? either by changing the script or changing the validated list?
    Thanks again for your help.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: compile error ambiguous name detected

    Try this,

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: compile error ambiguous name detected

    Andy,

    Your a legend thanks very much that code works great and its exactly what I require.

    Thanks again.

Closed 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