Edit Excel Form Textbox Fields To Allow Only Numbers
When creating a "Userform" for data entry in Excel, it is important to edit numeric fields in the textboxes to only allow numbers. Using the "SetFocus" property for a textbox can be problematic after an error "MsgBox" is issues on an "Exit" or "Update" event and you are trying to clear the field and then position the cursor back at the location where the error was encountered.
A better way to accomplish this behavior is with the "Cancel" flag which is part of the Exit event as illustrated in the code below.
You must provide a part of the form with a label called "lblErrorMessage" and initialize it with a value of "Must Be Numeric" and Visible = False. It is always good to create the label in RED.
Program Code
' **********************************************************************
' Here is an Exit event for a Textbox Used to Enter Numeric Fields
' In An Excel Form
' **********************************************************************
Private Sub txtSquareFeet_Exit(ByVal Cancel As MSForms.ReturnBoolean)
AllowOnlyNumbers
If strCancel = "Y" Then
Cancel = True
End If
End Sub
' **********************************************************************
' This is the Edit Routine Which Turns On the Error Message if Necessary
' And Clears the Non-Numeric Data And Positions the Cursor in the
' Same Field as the Error Occurred
' **********************************************************************
Private Sub AllowOnlyNumbers()
strCancel = "N"
Me.lblErrorMessage.Visible = False
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
Me.lblErrorMessage.Visible = True
.Value = vbNullString
strCancel = "Y"
End If
End With
End If
End Sub