excel - Validation message of text box entry on modeless form interrupts text selection

Question

Hi I try this code in my Userform to check if Data entered in textbox1 is a number and if is not show a msgbox to user and select text in textbox1, but below code doesn't select text in textbox1 when Userform is vBModeless

Private Sub TextBox1_Change()
    If Not IsNumeric(TextBox1) Then
        MsgBox " only number"
        TextBox1.SetFocus
        TextBox1.SelStart = 0
        TextBox1.SelLength = Len(TextBox1.Text)
    End If
End Sub

is any solution?

Answer 1

The root of the problem isn't a selection, since it there and works as expected:

Private Sub TextBox1_Change()
    If Not IsNumeric(TextBox1) Then
        MsgBox " only number"
        TextBox1.SetFocus
        TextBox1.SelStart = 0
        TextBox1.SelLength = Len(TextBox1.Text)
        Debug.Print TextBox1.SelText
    End If
End Sub

I think the fundamental problem here is that MSForms controls aren't real windows, but "windowless" entity without window handle (of course, there's exceptions like listbox, tabstrip, multipage), which easily can be tested via hidden method:

'Which obviously returns a zero.
Debug.Print TextBox1.[_GethWnd]

In other hand there's the Window's message-passing model where each control is a window (hence Windows OS) with a proper window handle and with ability to send and recive messages like WM_SETFOCUS/WM_KILLFOCUS and act appropriately. And back to MSForms - the UserForm manages all the interaction between outer world and child controls internally.

Let's start by declaring WIN API function GetFocus:

Public Declare Function GetFocus Lib "user32.dll" () As Long

And let's add some of Debug.Print's to see what is happening:

Private Sub TextBox1_Change()
    If Not IsNumeric(TextBox1) Then
        Debug.Print "--"
        Debug.Print GetFocus
        MsgBox " only number"
        Debug.Print GetFocus
        TextBox1.SetFocus
        Debug.Print GetFocus
        Debug.Print "--"
        TextBox1.SelStart = 0
        TextBox1.SelLength = Len(TextBox1.Text)
    End If
End Sub

Which yields this sequence:

--
 <userform hwnd>
 <outer hwnd>
 <outer hwnd>
--

As you can see - the SetFocus has no effect, because the Userform has no idea that focus is lost (hence there's no Exit event either). To overcome this problem you should explicitly lose your focus by transferring focus to another child control or by switching Enabled (or even Visible) property:

Private Sub TextBox1_Change()
    If Not IsNumeric(TextBox1) Then
        Debug.Print "--"
        Debug.Print GetFocus
        TextBox1.Enabled = False
        'or use CommandButton1.SetFocus or something
        MsgBox " only number"
        TextBox1.Enabled = True
        Debug.Print GetFocus
        TextBox1.SetFocus
        Debug.Print GetFocus
        Debug.Print "--"
        TextBox1.SelStart = 0
        TextBox1.SelLength = Len(TextBox1.Text)
    End If
End Sub

Which yields a desired appearance and a proper sequence:

--
 <userform hwnd>
 <outer hwnd>
 <userform hwnd>
--

As a conclusion, the cause is internal and external focus states got out of sync, which stems from a slightly different managment model between MSForms and WinForms/WinAPI plus a non-modal regime of work, that mixes them both, giving an opportunity to lose focus to something non-MSForms.