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.

Answer 2

In my version of Excel A msgbox is always vbModal, it cannot be vbModeless, you can only set its Modal scope property to be as application level or system level

• At Application level, it stops the application until it is responded
• At system level it suspends all applications until the user responds to it

In order to do what you intend to do; I have created a Modeless UserForm and use it as a message box

The code becomes

Private Sub TextBox1_Change()

    If Not IsNumeric(TextBox1) Then
        UserForm2.Label1 = "Only Number is Allowed"
        UserForm2.Show

        'At this point TextBox1 has lost focus,
        'to set the focus again you have to setfocus on something else
        'and then again set focus on textbox1 (a way to reinitialize it).
        'I have added a hidden textbox2 and will set focus on it

        TextBox2.Visible = True
        TextBox2.SetFocus
        TextBox2.Visible = False

        TextBox1.SetFocus
        TextBox1.SelStart = 0
        TextBox1.SelLength = Len(TextBox1.Text)

    End If

End Sub

The screenshot is only a test, you can do the formatting etc according to your application.