Problem - ActiveX ComboBox selection immediately vanishes.
Attached is a small Excel file.
I have wiped out application code for diagnosis.
The dropdown is on Source Sheet column W.
I have referenced it both ways (direct and through Validation_Name range name).
The result is linked to cell c6.
You can see that when you make a choice, the result is shown on A2 but then a second later vanishes.
I have spent stupidly 10 hours trying to determine went is wrong.
Please help.
Hi BruceMartin,
you were setting the source and the background colour well in the settings for the combobox. But you also need to change the fore colour because you wont see anything if the background AND also the fore colour is same, in your case Black.
Greetings
Tor
Your source range data is determined by the selected item but changing that changes the source range, which then reloads the list and clears the current selection. It's generally best to avoid activex controls anyway, but if you must use them, I'd recommend that you populate them in code, not by linking them directly to cells.
Rory
Yes, when you enter data in selection box, it changes source range data to narrow down possibilities. When you select something then only one item remains in the range data (namely in cell W2. That does not explain why the selection vanishes. What is causing the selection to vanish? A2 in sheet START shows the answer and that vanishes also but it is there for a moment.
"Your source range data is determined by the selected item but changing that changes the source range, which then reloads the list and clears the current selection."
Another test.
1. Put "abr" in the selection.
2. Hit down arrow and select "Test3"
3. Watch as A2 gets filled. Everything seems fine but a second later, the selection disappears even as A2 remains and W2 in Source shows the one selection.
What is making the dropdown selection change.
MRutor,
Forecolor and background color are not both black and even if I change them, that doesn't address issue. Thanks.
Thank you both for helping!
As I said, if you change the source list, the combobox reloads and its selection is cleared, which will also therefore clear the linked cell.
Thank you for your help (so nice).
Based on the 3 steps above, the end result is that A2 and C6 are the same as the selection. W2 in Source is the same as the selection. They do not change but the dropbox selection vanishes. I do not want the selection in the dropbox to vanish.
After selection, it has only once option that is W2. That selection should remain in the dropbox but it doesn't.
Therefore the end result should be:
The selection in the dropbox, A2 and C6 in Start and W2 in Source are ALL showing the same thing.
Note the screen shot.
Don't link the control directly to the ranges. Use code to fill the control (using its List property) and to populate the cell.
I want to follow your directions but I am a bit of a newbie. How do I use list property to populate the cell?
You don't, you use it to populate the list of values in the combobox. Personally, I wouldn't use the source cells that you are currently, I'd simply filter the array of list values in code. I don't have time to demonstrate currently, but there are examples of that on the forum if you search for something like filtering a listbox/combobox.
I will try to research. But, just to be clear, this technique will keep the selected value in the dropbox? I don't want to go through hours of learning this only to have the same problem. My Source column W changes perfectly based on a few letters in the dropbox and upon selection, it stays correct. The only issue is having the selection remain in the dropbox and not vanish.
Since you will have full control over when/if the list is refreshed, you can make it work however you need it to.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks