TextBox SetFocus Problem on UserForm

[jarekmos]

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:

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:

Private Sub UnitCost_Change()
Call OnlyNumbers
End Sub

Thanks in advance.

[Leith Ross]

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.


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

[jarekmos]

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.

[Leith Ross]

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-programming-vba-macros/1303264-textbox-setfocus-problem-on-userform.html