Combobox Question

[Roopher]

Dear Excel Enthusiasts,

I have a question regarding a combobox issue: On a VBA form, when I have finished the combobox input via pressing enter, the next form item (tab order determined) gets focussed. How can I achieve that the combobox remains in focus?

Cheers,

 

[al_b_cnu]

Hi,

Try

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ComboBox1.SetFocus
End Sub

 

[Roopher]

Hi, and thanks for the suggestion. Unfortunately, the exit of the event always tab-cycles to the next form control. Regardless of xxx.SetFocus statements.

 

[RoryA]

Can I ask why you want to do this? You would basically need to trap the KeyDown event and check if the KeyCode is 13, but if you want the Enter key to work normally when the list is dropped down, then you need to cater for that too.

 

[al_b_cnu]

What if you had a dummy textbox (made same colour as the userform & small as possible), with the Enter event doing a combobox1.setfocus?

 

[Roopher]

Dear rorya, have tried that, but every time a combobox is left, the next form control is focussed. A key trap with ComboBox1.SetFocus in it gets overwritten/de-done.

And, the reason is to have data input streamlined in a way that a combobox enter stroke does not remove focus from it, but rather executes a sub, and then the Combobox ist ready for immediate input again.

Cheers,

 

[RoryA]

I didn't say anything about SetFocus - you would alter the KeyCode to 0 in the event, thereby cancelling it.
And FWIW, I think that's a really bad idea, as you are more likely to end up with erroneous data when the user hits enter with the wrong thing selected by mistake. I would much prefer to have a button to press that runs the sub and then returns to the combo - you can assign it a shortcut key if required.

 

[Roopher]

Dear al_b_cnu,

tried that, too. But the ComboBox is left when a Enter stroke is received, and the focus then moves on to the next form item after this key stroke. I would come to the dummy combobox only when/before I leave the ComboBox, and after having executed the dummy combobox/sub, the programme flow will return to the original combox, and shifting the focus to the next form item.

 

[Roopher]

Dear rorja, thank you very very much, you really are an expert! Your hint has worked the way I wanted it. Great!