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

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.