how to keep focus on txt_box in userform

[Pierre via OfficeKB.com]

Hi,

i have the following code for a txt_box on a userform:

Private Sub nw_premieNP_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OnlyNumbers nw_premieNP
Worksheets("staffelberekening").Range("G12") = nw_premieNP.Value
End Sub

the Only numbers sub looks like

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(Left(.Value, 1)) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan", vbOKOnly, "De Pensioen
Adviseur"
.Value = vbNullString
.SetFocus
End If
End With
End Sub

The problem is that if the input is not numeric, the input is deleted but the
focus is put on the next input box.
How can i have the focus on the field that was input wrong ?

thanks,
Pierre

 

[Norman Jones]

Hi Jean-Pierre,

Try:

Option Explicit
Public blInvalidEntry As Boolean '<<=== Module level flag


Private Sub nw_premieNP_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OnlyNumbers nw_premieNP
Cancel = blInvalidEntry <<============ ADDED
Worksheets("Sheet2").Range("G12") = nw_premieNP.Value
End Sub

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(Left(.Value, 1)) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan", _
vbOKOnly, "De Pensioen Adviseur """
.Value = vbNullString
blInvalidEntry = True
Else
blInvalidEntry = False
End If
End With
End Sub

 

[Norman Jones]

Hi Jean-Pierre,

As an alternative apporoach, you might consider using the textbox's KeyPress
event to ensure numeric entry, e.g:

Private Sub nw_premieNP_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
MsgBox "Sorry, alleen getallen toegestaan", vbOKOnly, "De Pensioen"
KeyAscii = 0
End Select
End Sub

---
Regards,
Norman

Hi Jean-Pierre,

Try:

Option Explicit
Public blInvalidEntry As Boolean '<<=== Module level flag


Private Sub nw_premieNP_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OnlyNumbers nw_premieNP
Cancel = blInvalidEntry <<============ ADDED
Worksheets("Sheet2").Range("G12") = nw_premieNP.Value
End Sub

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(Left(.Value, 1)) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan", _
vbOKOnly, "De Pensioen Adviseur """
.Value = vbNullString
blInvalidEntry = True
Else
blInvalidEntry = False
End If
End With
End Sub

 

[Pierre via OfficeKB.com]

Hi Norman,

This seems to work but i still don't get the focus on the curent textbox.
In my code the cursor was blinking in the next txt_box.
With your code, i don't see a blinking cursor at all....
Is there a way tot select the 'invalid' code in the field so that the user
can give a new input?
thanks,
Pierre

Hi Jean-Pierre,

Try:

Option Explicit
Public blInvalidEntry As Boolean '<<=== Module level flag

Private Sub nw_premieNP_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OnlyNumbers nw_premieNP
Cancel = blInvalidEntry <<============ ADDED
Worksheets("Sheet2").Range("G12") = nw_premieNP.Value
End Sub

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(Left(.Value, 1)) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan", _
vbOKOnly, "De Pensioen Adviseur """
.Value = vbNullString
blInvalidEntry = True
Else
blInvalidEntry = False
End If
End With
End Sub

---
Regards,
Norman

Hi,

[quoted text clipped - 27 lines]

thanks,
Pierre

 

[Norman Jones]

Hi Pierre,

With your code, i don't see a blinking cursor at all....

Try restoring your original SetFocus instruction in the OnlyNumbers sub,
i.e.:

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(Left(.Value, 1)) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan", _
vbOKOnly, "De Pensioen Adviseur """
.Value = vbNullString
.SetFocus ' <<=========== Restored
blInvalidEntry = True
Else
blInvalidEntry = False
End If
End With
End Sub

---
Regards,
Norman

Hi Norman,

This seems to work but i still don't get the focus on the curent textbox.
In my code the cursor was blinking in the next txt_box.
With your code, i don't see a blinking cursor at all....
Is there a way tot select the 'invalid' code in the field so that the user
can give a new input?
thanks,
Pierre

Hi Jean-Pierre,

Try:

Option Explicit
Public blInvalidEntry As Boolean '<<=== Module level flag

Private Sub nw_premieNP_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OnlyNumbers nw_premieNP
Cancel = blInvalidEntry <<============ ADDED
Worksheets("Sheet2").Range("G12") = nw_premieNP.Value
End Sub

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(Left(.Value, 1)) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan", _
vbOKOnly, "De Pensioen Adviseur """
.Value = vbNullString
blInvalidEntry = True
Else
blInvalidEntry = False
End If
End With
End Sub

---
Regards,
Norman

Hi,

[quoted text clipped - 27 lines]

thanks,
Pierre

 

[Dave Peterson]

You have an alternative at your first post.

Hi,

i have the following code for a txt_box on a userform:

Private Sub nw_premieNP_Exit(ByVal Cancel As MSForms.ReturnBoolean)
OnlyNumbers nw_premieNP
Worksheets("staffelberekening").Range("G12") = nw_premieNP.Value
End Sub

the Only numbers sub looks like

Private Sub OnlyNumbers(ctl As Object)
With ctl
If Not IsNumeric(Left(.Value, 1)) And .Value <> vbNullString Then
MsgBox "Sorry, alleen getallen toegestaan", vbOKOnly, "De Pensioen
Adviseur"
.Value = vbNullString
.SetFocus
End If
End With
End Sub

The problem is that if the input is not numeric, the input is deleted but the
focus is put on the next input box.
How can i have the focus on the field that was input wrong ?

thanks,
Pierre