TextBox SetFocus Problem on UserForm

Hi, I am having trouble with the setfocus command and I have done a bit of searching but my knowledge of vba is very little.

I would like a number value to be entered in TextboxControl and on typing a value if the data is not in the correct format a message will appear with an OK Button. When OK is selected I would like the wrong data to be cleared and the cursor back in the TextboxControl.

The code below is working fine except the SetFocus doesn't appear in the activate control.

Here is the code in Module mode:

Code:

---

Sub OnlyNumbers()

    If TypeName(ExpensesForm.ActiveControl) = "TextBox" Then

        With ExpensesForm.ActiveControl
            If Not IsNumeric(.Value) And .Value <> vbNullString Then
                ExpensesForm.ActiveControl.BackColor = &HC0C0FF 'read

                MsgBox "Sorry, only numbers allowed"

                .Value = vbNullString
                ExpensesForm.ActiveControl.BackColor = &HFFFFFF
                ExpensesForm.ActiveControl.SetFocus
            End If
        End With

    End If

End Sub

---

Here is the code in Userform mode:

Code:

---

Private Sub UnitCost_Change()
Call OnlyNumbers
End Sub

---

Thanks in advance.

Hello jarekmos,

You will need to use 2 events to accomplish your goal. If you have multiple TextBoxes you want to apply this to then this can be done by Sub-Classing the TextBox controls.

Here is an Example using TextBox1. The AfterUpdate event fires once The Enter key has been pressed or another control has been clicked. This will validate if the entry is a valid number and set the Control's Tag property to "False" if it fails. The Exit event will examine the Tag. If it is "False" it then display the error message, select the text in the control, and "Cancel" the auto-tab to the next control. This keeps the focus on the TextBox.

Code:

---

Private Sub TextBox1_AfterUpdate()
    If Not IsNumeric(TextBox1.Value) Then
        TextBox1.Tag = "False"
    End If
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox1.Tag = "False" Then
        MsgBox "Only numbers are allowed."
        TextBox1.Tag = ""
        TextBox1.SelStart = 0
        TextBox1.SelLength = Len(TextBox1.Value)
        Cancel = True
    End If
End Sub

---

Thanks Ross for your response, but it is not quite what I was looking for.

My code works fine, so I would prefer to keep it, but have it modified a little. The problem still keeps on recurring. Whenever I get an error message and I press OK on it the cursor doesn't jump right back to the active textbox, instead I have to use my mouse to click on the textbox.

I also have a lot of textboxs, so when I press the enter key on one textbox then the cursor would jump to the next, that is why I don't want a code specifically for one textbox.

Hello jarekmos,

As I stated if you have many TextBoxes then the events can be shared using a technique known as Sub-Classing.

NOTE: This question was also cross-posted here https://www.excelforum.com/excel-pro...-userform.html