Override .SetFocus

[mikeymay]

I have a userform that has a number of textboxes.

Some of the textboxes require a numeric value to be entered and I perform a check on the entry with the _afterupdate event.

If the textbox entry isn't numeric, a message box pops up, the textbox is cleared and I want that textbox to hold the focus as it needs an entry. I have the textbox.SetFocus in the _afterupdate code but as I am using TabStop values for if the user uses the TAB key to navigate through the form, the .SetFocus is being ignored.

Private Sub txtLoanCount_afterupdate()

If IsNumeric(txtLoanCount) = False Then
   MsgBox ("Enter a numeric value of 1 to 5 only"), vbExclamation, "ERROR"
   
   txtLoanCount = ""
   
   txtLoanCount.SetFocus
   
   Exit Sub
   Else
End If
   
strControl = mpCaseDetails(mpCaseDetails.Value).ActiveControl.Name

CheckForm

End Sub

 

[dmt32]

Hi,
rather than perform check after user has entered a value in textbox you can just prevent anything other than numeric values (0 - 9) being entered

Try this

Place following in Standard module if you need to use it with other userforms

Function NumbersOnly(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    Select Case KeyAscii
'valid entries [0-9]
    Case 48 To 57
'valid
    Case Else
'cancel
        KeyAscii = 0
    End Select
    Set NumbersOnly = KeyAscii
End Function

and to call it

Private Sub txtLoanCount_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'allow numeric only
    KeyAscii = NumbersOnly(KeyAscii)
End Sub

Solution should only allow Numeric values to be entered in your Textbox

Hope Helpful

Dave

 

[mikeymay]

Thanks Dave!

Plugged that in and it works a treat!

I have two other textboxes where I need dates enetering
Start Date - To be no later than current date less 90 days
End Date - To be greater then the start date and no later then current date

Also a textbox the enter a numeric value > 0 and to two decimal places.

Could a similar function be used for these as well?

 

[dmt32]

Hi,
to allow dates that include "/" and decimal point "." to be entered in textboxes that call the function, you just include them in the values allowed in function

You need to add 46, 47 as shown.

'valid entries [0-9] "." "/"
    Case 46, 47, 48 To 57

To limit the date values your user enters, you would use the controls other events

For example, add following additional codes to your userform

Private Sub txtLoanCount_Change()
    With Me.txtLoanCount
        .BackColor = IIf(Val(.Value) > 5, vbRed, vbWhite)
    End With
End Sub
Private Sub txtLoanCount_Enter()
    Me.txtLoanCount.ControlTipText = "Enter Number Between 1 and 5 Only"
End Sub
Private Sub txtLoanCount_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = Val(Me.txtLoanCount.Value) > 5
End Sub

for the txyLoanCount Textbox, codes limit value > 5 being entered & turns red if this value is exceeded - Also, I added control tip when user enters the control which displays what values that should be entered.

Just apply same approach with your date textboxes but do be mindful that a date in a textbox is a string & you should coerce it in to a date datatype using CDate or DateValue function to perform the numeric test.

Dave