Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combobox: best match on partially typed content?
I've added a combobox to my userform. Exact match is required.
When I begin typing, if there is one or more exact match, it will pre-fill the combobox text with the first possible exact match. Once I am close to the target selection, I can just click the drop-down arrow and select my target item. However, if I mis-type or am a letter off of a valid selection, when I click the down arrow, instead of being at the last acceptable match, it puts me back at the top of the list. In this particular case, the combobox may have several thousand entries, and the spelling of some items are not intuitive. Is there any way to force the combobox to stay at the last found match? For example, to programmatically capture the last valid match (which the combobox has somewhere, because it is pre-populating it), and then use that value to force the combobox location even if the currently typed text is not a valid match for anything on the list? For example, if the desired match is "G425 p2 Electric" and another item is "G425 e Electric" then I get a match up through typing "G425 ". If I stop there and hit the combobox dropdown, I'll see my match in the list near the exact match I'm on. However, if I type "G425 p El" because I didn't know there was a "2" on this item, it plops me back at the very beginning of the list, instead of still being in the part of the list where I could easily find the target item. So, I'm looking for how to capture the valid match item ID (each time a match is made), and how to force the combobox to go to that location when the dropdown is selected, even if the current text is not an exact match. Thanks! Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combobox: best match on partially typed content?
You would have to build an error trap into the code that is controlling the
match up, if you are using code to do the match. Without seeing what code is being used, it is difficult to offer any specific coding suggestions. "ker_01" wrote in message ... I've added a combobox to my userform. Exact match is required. When I begin typing, if there is one or more exact match, it will pre-fill the combobox text with the first possible exact match. Once I am close to the target selection, I can just click the drop-down arrow and select my target item. However, if I mis-type or am a letter off of a valid selection, when I click the down arrow, instead of being at the last acceptable match, it puts me back at the top of the list. In this particular case, the combobox may have several thousand entries, and the spelling of some items are not intuitive. Is there any way to force the combobox to stay at the last found match? For example, to programmatically capture the last valid match (which the combobox has somewhere, because it is pre-populating it), and then use that value to force the combobox location even if the currently typed text is not a valid match for anything on the list? For example, if the desired match is "G425 p2 Electric" and another item is "G425 e Electric" then I get a match up through typing "G425 ". If I stop there and hit the combobox dropdown, I'll see my match in the list near the exact match I'm on. However, if I type "G425 p El" because I didn't know there was a "2" on this item, it plops me back at the very beginning of the list, instead of still being in the part of the list where I could easily find the target item. So, I'm looking for how to capture the valid match item ID (each time a match is made), and how to force the combobox to go to that location when the dropdown is selected, even if the current text is not an exact match. Thanks! Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combobox: best match on partially typed content?
Hi
Set its Style property to 2 - List, and it will hopefully behave as desired. HTH. Best wishes Harald "ker_01" wrote in message ... I've added a combobox to my userform. Exact match is required. When I begin typing, if there is one or more exact match, it will pre-fill the combobox text with the first possible exact match. Once I am close to the target selection, I can just click the drop-down arrow and select my target item. However, if I mis-type or am a letter off of a valid selection, when I click the down arrow, instead of being at the last acceptable match, it puts me back at the top of the list. In this particular case, the combobox may have several thousand entries, and the spelling of some items are not intuitive. Is there any way to force the combobox to stay at the last found match? For example, to programmatically capture the last valid match (which the combobox has somewhere, because it is pre-populating it), and then use that value to force the combobox location even if the currently typed text is not a valid match for anything on the list? For example, if the desired match is "G425 p2 Electric" and another item is "G425 e Electric" then I get a match up through typing "G425 ". If I stop there and hit the combobox dropdown, I'll see my match in the list near the exact match I'm on. However, if I type "G425 p El" because I didn't know there was a "2" on this item, it plops me back at the very beginning of the list, instead of still being in the part of the list where I could easily find the target item. So, I'm looking for how to capture the valid match item ID (each time a match is made), and how to force the combobox to go to that location when the dropdown is selected, even if the current text is not an exact match. Thanks! Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combobox: best match on partially typed content?
Wow, that did exactly what I asked for! Unfortunately, it also showed me that
I wasn't asking quite the right question (or perhaps, I'm just too picky). It now stays at the proper part of the list, even when the typing no longer matches an entry. Unfortunately, the typing area only shows the best match, not what is being typed, which makes it harder to use- if I type a partial title, then hit the backspace once, the list match goes back to the top of the list, but behind the scenes it does not appear to have cleared the rest of the typed entry. I guess what I was hoping for was to have the main entry area act like the style 0= fmstyledropdowncombo, but have the actual dropdown list act like it does in your suggestion, using style 2= fmstyledropdownlist. I guess I can't have the best of both worlds, without getting into some harder core programming :( JLG- thank you for your response to the thread as well. I was just trying to wrangle the existing behavior of the combobox, I haven't actually programmed a replacement for it yet- so I don't have any code. I was thinking that snagging the activities associated with the combobox (if they are exposed/accessible to VBA) would have been easier than trying to create a replacement combobox from scratch. Thanks, Keith "Harald Staff" wrote: Hi Set its Style property to 2 - List, and it will hopefully behave as desired. HTH. Best wishes Harald "ker_01" wrote in message ... I've added a combobox to my userform. Exact match is required. When I begin typing, if there is one or more exact match, it will pre-fill the combobox text with the first possible exact match. Once I am close to the target selection, I can just click the drop-down arrow and select my target item. However, if I mis-type or am a letter off of a valid selection, when I click the down arrow, instead of being at the last acceptable match, it puts me back at the top of the list. In this particular case, the combobox may have several thousand entries, and the spelling of some items are not intuitive. Is there any way to force the combobox to stay at the last found match? For example, to programmatically capture the last valid match (which the combobox has somewhere, because it is pre-populating it), and then use that value to force the combobox location even if the currently typed text is not a valid match for anything on the list? For example, if the desired match is "G425 p2 Electric" and another item is "G425 e Electric" then I get a match up through typing "G425 ". If I stop there and hit the combobox dropdown, I'll see my match in the list near the exact match I'm on. However, if I type "G425 p El" because I didn't know there was a "2" on this item, it plops me back at the very beginning of the list, instead of still being in the part of the list where I could easily find the target item. So, I'm looking for how to capture the valid match item ID (each time a match is made), and how to force the combobox to go to that location when the dropdown is selected, even if the current text is not an exact match. Thanks! Keith |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform combobox: best match on partially typed content?
Hi again Keith
You may want to test an approach like this one too: http://www.dicks-blog.com/archives/2...mit-a-listbox/ http://www.dailydoseofexcel.com/arch...ifferent-view/ but VBA may be too slow if you deal with very long lists combined with fast typing users :-) Best wishes Harald "ker_01" skrev i melding ... Wow, that did exactly what I asked for! Unfortunately, it also showed me that I wasn't asking quite the right question (or perhaps, I'm just too picky). It now stays at the proper part of the list, even when the typing no longer matches an entry. Unfortunately, the typing area only shows the best match, not what is being typed, which makes it harder to use- if I type a partial title, then hit the backspace once, the list match goes back to the top of the list, but behind the scenes it does not appear to have cleared the rest of the typed entry. I guess what I was hoping for was to have the main entry area act like the style 0= fmstyledropdowncombo, but have the actual dropdown list act like it does in your suggestion, using style 2= fmstyledropdownlist. I guess I can't have the best of both worlds, without getting into some harder core programming :( JLG- thank you for your response to the thread as well. I was just trying to wrangle the existing behavior of the combobox, I haven't actually programmed a replacement for it yet- so I don't have any code. I was thinking that snagging the activities associated with the combobox (if they are exposed/accessible to VBA) would have been easier than trying to create a replacement combobox from scratch. Thanks, Keith "Harald Staff" wrote: Hi Set its Style property to 2 - List, and it will hopefully behave as desired. HTH. Best wishes Harald "ker_01" wrote in message ... I've added a combobox to my userform. Exact match is required. When I begin typing, if there is one or more exact match, it will pre-fill the combobox text with the first possible exact match. Once I am close to the target selection, I can just click the drop-down arrow and select my target item. However, if I mis-type or am a letter off of a valid selection, when I click the down arrow, instead of being at the last acceptable match, it puts me back at the top of the list. In this particular case, the combobox may have several thousand entries, and the spelling of some items are not intuitive. Is there any way to force the combobox to stay at the last found match? For example, to programmatically capture the last valid match (which the combobox has somewhere, because it is pre-populating it), and then use that value to force the combobox location even if the currently typed text is not a valid match for anything on the list? For example, if the desired match is "G425 p2 Electric" and another item is "G425 e Electric" then I get a match up through typing "G425 ". If I stop there and hit the combobox dropdown, I'll see my match in the list near the exact match I'm on. However, if I type "G425 p El" because I didn't know there was a "2" on this item, it plops me back at the very beginning of the list, instead of still being in the part of the list where I could easily find the target item. So, I'm looking for how to capture the valid match item ID (each time a match is made), and how to force the combobox to go to that location when the dropdown is selected, even if the current text is not an exact match. Thanks! Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
C# Excel cell content partially strikeout | Excel Programming | |||
Need rows in Column A removed if they fully or partially match with any Column B row | Excel Discussion (Misc queries) | |||
Userform: Combobox, dates, match required- can't get date format to work | Excel Programming | |||
How do i refer to the text typed in a combobox? | Excel Programming | |||
ComboBox, referring to content | Excel Programming |