Tukukkk

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?

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.

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.

I vote for usmanhaq and CommonSense!

just something to add: I've tried to implement similiar thing on one of my projects, I end up avoiding pop up another window. Just use a label to alert.

And after i implement this i find this more userfriendly!

Hope this helps!

userform:

Private Sub TextBox1_Change()

If Not IsNumeric(TextBox1.Value) Then

    Label1.Caption = "NUMBER ONLY!"

    UserForm1.TextBox1.SetFocus

    UserForm1.TextBox1.SelStart = FirstNonDigit(TextBox1.Value) - 1

    UserForm1.TextBox1.SelLength = Len(TextBox1.Text)

Else

     Label1.Caption = ""

End If

End Sub

this function is funded online that would help highlight starting from the first non number

Public Function FirstNonDigit(xStr As String) As Long

    Dim xChar As Integer

    Dim xPos As Integer

    Dim I As Integer

    Application.Volatile

    For I = 1 To Len(xStr)

        xChar = Asc(Mid(xStr, I, 1))

        If xChar <= 47 Or _

           xChar >= 58 Then

            xPos = I

            Exit For

        End If

    Next

    FirstNonDigit = xPos

End Function