Combo Box on user form help pls

[The Gunslinger]

Hi guys,
Got a little problem, not least of which is that i havent worked in excel vba for ages, so i'm super rusty and forgotten half of the little i knew in the first place !

anyway, i have a button on a sheet in a work book, which when selected opens an input box for the user to enter a word or phrase, the code then searches a particular column for a match and then transfers the row records for each match on that sheet to a new sheet, and also the searched phrase to the same sheet as part of the title text.

This works perfectly well, however, i'm now trying to update it and replace the inputbox with a userform with a combobox, so that i can auto populate the list in the combo box with all of the available "phrases" that the user is likely to search by, so as they type it auto suggests entries, this removes the human error of misspelling etc.

I have this working perfectly well..... however, once i have selected or typed the entry i have 2 issues

1. I'd like the combobox to respond to the enter key instead of just tabbing to the "Go" button on the userform
and
2. This is the big error, i can't seem to get the combobox selection, the final value if you like transfered back to my original code.

The original code is in the worksheet section for the sheet the command button is embedded, and obviously the userform is in the userforms module, i can call the userform module easily by using the .show command in place of my original inputbox code "tSearchRslts = Inputbox....", but once the userform is closed, it does not return to my worksheet code and continue on from that point

any help would be appreciated, i'm sorry i dont have a copy of the code right now as i forgot to email it home form work lastnight.

I will continue to search through the forum for an answer, but many of the results deal with transferring the selection back into the worksheet, and i dont want that

 

[Norie]

Why not just hide the userform instead of unloading it?

It will still be available in memory and once you have done what you need with it then you can unload it.

That would eliminate the need for public variables.

PS I did notice that you mentioned Hide but you didn't post the code you were trying.

PPS Why are you creating a new instance of the form and why the DblCick?

 

[The Gunslinger]

ok, lets see.....

I mentioned hide, because i was having trouble with unload, so was initially using hide, that turned out to be a typo on my part ... oops!

Dont need it hanging around in memory, it's not used that much

The DblClick is so that i can select from the combobox drop down list, and hit enter instead of having to press the Go/ok button ... just seems more logical to me to have that function.

The code i tried here at home on a basic form was this posted here...
http://www.mrexcel.com/forum/showpost.php?p=2401491&postcount=7

and that worked fine, my initial problems when trying to do this on my own was that i was not getting the value transferred out of the user form to the string value, that now seems to be working, but the double click isnt.

I was basically trying to take a simple route in replacing the original msgbox line in my code without having ot change too much else, one other thing i have that is odd, and it's not related to this code, is that for some reason when testing, i typed in "ss" and it searched the form for that and returned unrelated results,but that looks like my other code misbehaving !... it was only supposed to search one column and find exact results,

I'll look more at this next week when i'm on nights, and make sure i havent made some silly mistake somewhere, i was just curious if andrews' method was simpler

 

[Norie]

Who said anything about the userform hanging around in memory?

Hide it, do what you need with it, eg get data from it and once you are finished with it unload it.

I don't see how using the double click event would help with being able to use enter instead of clicking a button.

If you want a button's click event code when you press enter on the form it's on just set it's Default property to Yes (or is it true?)

I think somebody already suggested that.

If you want something to happen as soon as a value is picked from the combobox you should probably use it's change event.

 

[The Gunslinger]

yeah, that "true" value is looking likely to be the culprit, probably me guilty of not reproducing the same settings on the file at work as i did here at home, need to concentrate on this at work rather than home and see how it comes out