[SOLVED] compile error ambiguous name detected

[truman40]

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.

[dominicb]

Good afternoon truman40

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.

[truman40]

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?

[truman40]

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.

[truman40]

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

[truman40]

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

[Andy Pope]

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.

[truman40]

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.

[Andy Pope]

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?

[truman40]

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.

[Andy Pope]

Try this,

Please Login or Register  to view this content.

[truman40]

Andy,

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

Thanks again.