activex combobox to trigger automatic sheet code

[kosherboy]

Hello,
I'm trying to make harmony between the two codes below
The following code enters the value from the combobox directly into cell D32:

Please Login or Register  to view this content.

...and as for this code, if a value is entered in column D it detects through the range if there is a duplicate of that value and then displays a message box and clears the target cell (in this case it's cell D32). I hope that since cell D32 is linked to Combobox1 then ComboBox1 would automatically clear as well

Please Login or Register  to view this content.

Thanks in advance

[gmr4evr1]

I believe that cell D32 is based of the combobox, not the other way around so if D32 is cleared, it wont clear the combobox. This will though, add it to your 2nd code

Please Login or Register  to view this content.

[kosherboy]

Ok thank you.
i apologize but i failed to mention that the second code doesn't even execute at all.
I was thinking faster than i was typing

[gmr4evr1]

I used your codes in a blank workbook and it did work correctly. Where did you put the codes?
Edit* It also closes and clears the combobox.

[kosherboy]

Here is an example workbook Cmd-kosherboy.xlsm
Choose a name from the dropdown that already exists in column D.
Code should remove it since its a duplicate but seems that the code is ignoring the 2nd code.

[gmr4evr1]

I think I know what the problem is. The combobox isn't actually part of the sheet and the value being placed into D32 is not being placed there manually, therefore it is not reading it as a change. If you select a name in the combobox then click in a cell on the sheet, the code runs fine, but, I know this isn't an ideal way to do this. When I did it myself, I put the combobox on a userform, that's why it worked for me. Replace your first code with this..

Please Login or Register  to view this content.

[kosherboy]

Thanks again for your reply gmr.
The reason why i don't use the default 'change' event for the combobox is because i have a code that i wrote that must need be executed only once i go out of the combobox. In other words: lose focus, exit the combobox etc. I don't want to complicate matters too much but the code will not work properly whilst the user is still in the combobox.
I searched everywhere online and i didn't see any talk about a "before Update" on active x controls

[Norie]

Selecting a value from the combobox doesn't trigger the BeforeUpdate event, and that might be because an ActiveX combobox on a worksheet doesn't have such an event.

What exactly are you trying to achieve?

[kosherboy]

Selecting a value from the combobox doesn't trigger the BeforeUpdate event, and that might be because an ActiveX combobox on a worksheet doesn't have such an event.

What exactly are you trying to achieve?

Hi Norie,
Basically i have a sheet with dozens of active x comboboxes, as soon as a value is selected from the dropdown, it's linked cell in column D is populated and then the below code runs:

Please Login or Register  to view this content.

The problem that i am having is the following: let's say i selected "Jack" from the 1st combobox and then i started typing out "Jill" in the 2nd combobox, as soon as i type in the "J" for Jill the code runs since the combobox displays the first choice for "J" which is "Jack" and which would result in the combobox getting cleared via the code, i could avoid this if i just select "Jill" from the combobox but this is not the ideal situation since the drop down menu goes on forever.
I know this wouldn't be a problem in userforms since the boxes in userforms have the option of triggering a code only when the user selects out of the box, in my first post i was under the assumption that active x controls work the same way as userform controls.
Maybe there is a workaround?

[nilem]

maybe so

Please Login or Register  to view this content.

Previously set the LincedCell property to ""

[kosherboy]

Thanks nilem,
Ideally i was trying to avoid putting the code in the combobox because then i would have to copy it into every combobox and i have 50 of them! but i will worry about that later...
Anyway, i copied your code to the next comboxbox like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

..but the msgbox got displayed and cleared the cell even when i entered the first value into combobox1?

[nilem]

probably need a sample of your file with multiple combobox'es

[Leith Ross]

Hello Kosherboy,

This code worked on the example workbook you posted. This can be adapted to other ComboBoxes as well.

Please Login or Register  to view this content.

[kosherboy]

Hi Leith,
Thanks for your response.
Here is the workbook uploaded again Cmd-kosherboy version2.xlsm
Now, try to type in the second combobox "Jill", you will see that since the combobox displays the first option for J (which in this case is jack) the code will run right away and clear the box even though i want to type in jill.
Also, it seems that the sheet's change procedure runs twice and bugs on the second try

[Leith Ross]

Hello kosherboy,

The behaviour you see is because the ComboBox is tied to the worksheet's range "N1:N4". Excel imposes it's auto-matching rules on what you type into the ComboBox as if you were typing into a cell.

While using the ListFillRange property is easy, it has unintended consequences for controlling the ComboBox. This is one of them.

If you explain what you are trying to achieve, it will be easier to provide a solution.

[kosherboy]

That actually makes sense, didn't think of that.
I would like for the code to run after a complete match is either MANUALLY typed into the combobox or selected from the dropdown.
I noticed that the automatch would highlight in blue the rest of the value, is there a way to detect if there is no blue highlighted characters?

[Norie]

You can check if a value from the list has been selected/entered by looking at the ListIndex property of the combobox.

If that's -1 a value from the list hasn't been selected/entered.

[kosherboy]

Hi Everybody!
i am very excited to share with you all that i have found a solution to this ongoing 2-week-old excel problem
i need to change

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

This solves a big portion of the issues i have been having!
Thank you all for the time spent on this, it is much appreciated

[gmr4evr1]

Glad you were able to get it worked out.