Override .SetFocus

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,480
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.
VBA Code:
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,393
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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

VBA Code:
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

Well-known Member
Joined
Jan 17, 2006
Messages
1,480
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,393
Office Version
  1. 2019
Platform
  1. Windows
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.
VBA Code:
'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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top