Re-Focusing on an Excel Userform ComboBox

Question

A User is Entering Data into the Edit Region of a ComboBox

The ComboBox's Change Event is used to run the following Code;

    AppActivate "Microsoft Excel"
        :
    'Do some stuff
        :
    UserForm1.Show
    UserForm1.ComboBox1.SetFocus

This works OK, BUT; even though the ComboBox now has the Focus again (according to the Userform.ActiveControl anyway), it has no Insertion Pointer to indicate this fact, and the User has to Re-Select the ComboBox before he can continue entering Data

I would like to have it so that the Insertion Pointer reappears and the User can continue Entering Data directly, without having to Re-Select the ComboBox

Adding the following line of Code

    SendKeys "{TAB}+{TAB}{RIGHT}"

is one solution, but it is messy (it generates ComboBox Events and using SendKeys is best avoided if possible anyway)

Can anyone suggest a better solution?

Q. Why do black sheep eat less than white sheep?

A. Because there aren't as many of them

Answer 1

the thing is , the userform itself might not have focus over the worksheet or other userforms.

Try this code:

with UserForm1.ComboBox1
    .Visible = False
    .Visible = True
    .setfocus
end with

or more generally to set properly focus, call this sub:

Sub Focus_ControlOfUserForm(ByRef Obj As Object) 'from the Userform, call Focus_ControlOfUserForm(Me)
Dim ctl As Control
With Obj
    Set ctl = .ActiveControl
    If TypeName(ctl) = "MultiPage" Or TypeName(ctl) = "Frame" Then
        Set ctl = ctl.SelectedItem.ActiveControl.Name
    End If

    With ctl
        Dim Af As Boolean
        With Application
            Af = .ScreenUpdating
            .ScreenUpdating = False
        End With
        '.SetFocus
        .Visible = False
        .Visible = True
        .SetFocus 
        If Af Then Application.ScreenUpdating = True
    End With
End With
End Sub

I also use that kind of code to move focus to the Form when i need ControlTipText showing on hover. If the Form has no focus, the Text bubbles won't show on mouse hover...

Answer 2

At the end of the UserForm Change Event add the code:

Application.OnTime Now + TimeValue("00:00:01"), "GetComboBoxFocus", , True

Inside a Module add the code:

Sub GetComboBoxFocus()
    UserForm1.ComboBox1.SetFocus
End Sub

I've run into this several times. For some reason it will work when being called from a module. The One Second is hardly noticeable to the end user. If you prefer, you can also add it earlier in the event code, it will wait until the current routine is finished and then run immediately following (1 second - time taken for the remaining code). Hope this helps.

Answer 3

After

UserForm1.ComboBox1.SetFocus

add

UserForm1.ComboBox1.SelStart = 0 'set the selected text starting position to 0