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

 

[Andrew Poulsom]

It would be better to see your code, but you can use the ComboBox's Change event procedure to transfer your data (rather than your current macro). If you Unload a UserForm the values in its controls are lost.

 

[The Gunslinger]

can't get to the code right now as it's at work and i'm off for 4 days... meant to email it home and forgot!

Unloading the userform is something i had trouble with last night, so was using the ".hide" option but was trying to transfer the value to a textstring prior to that, and that wasnt working.

Change event procedure never crossed my mind, and could be where i was going wrong, i think i'll make up a test workbook with similar coding and methoology to see if i can get it to work, at least i'll have something to play around with

 

[mikerickson]

If you set the .Default property of the OK button to True.
And have the combobox's DoubleClick event to call butOK_Click.
Then the user can indicate OK by either clicking on OK, pressing Enter, or DoubleClicking their selection.

 

[The Gunslinger]

ok ,thanks mikerickson, that solved the enter issue, cheers.

here's is a very simplified version, if i can get the selected value back into the 3rd message box (title test3) then i'm good to go, as all of my previous code that deals with the search and transfer of records from one sheet to the other is all contained in that CommandButton1_Click macro code, and is all working.

Worksheet button code

Private Sub CommandButton1_Click()
Dim rslt As String
    UserForm1.Show
    response = MsgBox(rslt, vbOKOnly, "test3")
End Sub

userform code

Private Sub UserForm_Initialize()
    With ComboBox1
        .Clear
        .AddItem " Phrase 1 "
        .AddItem " Phrase 2 "
        .AddItem " Phrase 3 "
    End With
End Sub


Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CommandButton1_Click
End Sub


Private Sub CommandButton1_Click()
Dim rslt As String
    rslt = ComboBox1.Value
    response = MsgBox(rslt, vbOKOnly, "test1")
    Unload UserForm1
    response = MsgBox(rslt, vbOKOnly, "test2")
End Sub

this works right up to the 3rd message box, where the resulting text is always blank

 

[Andrew Poulsom]

To make rslt available to all procedures move this:

Dim rslt As String

into a General module and remove any declarations in procedures.

 

[The Gunslinger]

ok i got it .... with help from this thread

http://www.mrexcel.com/forum/showthread.php?t=484736

here's what i did... not the tidiest of methods, but it should work as a direct replacement/integration into exisitng code with no messing around

~Button calling code from worksheet section

Private Sub CommandButton1_Click()
Dim rslt As UserForm1
    Set rslt = New UserForm1
    rslt.Show
    MsgBox rslt.ComboBox1.Value
    Set rslt = Nothing ' put at very end of other code
End Sub

~UserForm Code

Private Sub UserForm_Initialize()
    With ComboBox1
        .Clear
        .AddItem " Phrase 1 "
        .AddItem " Phrase 2 "
        .AddItem " Phrase 3 "
    End With
End Sub


Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CommandButton1_Click
End Sub


Private Sub CommandButton1_Click()
Dim result As String
    result = ComboBox1.Value
    response = MsgBox(result, vbOKOnly, "test1")
    Unload Me
End Sub

EDIT:
sorry andrew forgot to refresh before replying ! and didnt see it before as i was flitting from search results to excel ! but yes, i now see where your coming from with that last post .... cheers

 

[The Gunslinger]

Andrew, can you spell out the instructions for me regarding your last post, i was playing around with this last night to see if your idea was cleaner and a simpler method, but couldn't get it to run right, i'm obviously doing something simple wrong !

 

[Andrew Poulsom]

Here's an example:

'General module
 
Public rslt As String
 
'Worksheet module
 
Private Sub CommandButton1_Click()
    UserForm1.Show
    response = MsgBox(rslt, vbOKOnly, "test3")
End Sub
 
'UseForm module
 
Private Sub UserForm_Initialize()
    With ComboBox1
        .Clear
        .AddItem " Phrase 1 "
        .AddItem " Phrase 2 "
        .AddItem " Phrase 3 "
    End With
End Sub
Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CommandButton1_Click
End Sub
Private Sub CommandButton1_Click()
    rslt = ComboBox1.Value
    response = MsgBox(rslt, vbOKOnly, "test1")
    Unload UserForm1
    response = MsgBox(rslt, vbOKOnly, "test2")
End Sub

 

[The Gunslinger]

yeah, at a quick glance that looks like what i tried.... hmmm, will look closer at this when i get back to work next week, because the using enter in the combo box as suggested above to select the item and continue, failed to work on the excel file at work, but worked here at home !

thanks for the reply, i'll let you know how i get on