Error Trapping on Data Entry

[pdberger]

Good morning --

I'm creating a form to accept user info, and don't understand how to "scrub"
the input data in VBA. For example, I'd want an appropriate error message if
the user put a letter in a phone number.
In VB, I'd use the "e.cancel = true" approach, but don't know how that works
in VBA for Excel. In VB, I'd create the Sub using "ByVal e as
System.EventArgs", but that doesn't seem to be an option here. I'm using
Excel 2003.

Thanks in advance for any guidance.

 

[Dave Peterson]

You're using a textbox to get the phone number?

You could use the _beforeUpdate event to not allow the user to leave the textbox
until it's ok. But those checks are up to you to write--there's no edit mask
you can apply to the textbox like in some languages/controls.



Option Explicit
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

Dim lCtr As Long
Dim IsOk As Boolean

IsOk = True
With Me.TextBox1
If Trim(.Value) = "" Then
IsOk = False
Else
For lCtr = 1 To Len(.Value)
If IsNumeric(Mid(.Value, lCtr, 1)) Then
IsOk = False
'stop looking
Exit For
End If
Next lCtr
End If
End With

If IsOk = True Then
Me.Label1.Caption = ""
Else
Cancel = True 'don't let them leave the textbox
Me.Label1.Caption = "Error in Textbox1"
End If

End Sub
Private Sub UserForm_Initialize()

With Me.Label1
.Caption = ""
'any other settings
End With

With Me.CommandButton1
.Caption = "Ok"
.Default = True
End With

With Me.CommandButton2
.Caption = "Cancel"
.Cancel = True
.TakeFocusOnClick = False
End With

End Sub

The ".takefocusonclick = false" makes sure that the user can still click on the
Cancel key without validating the phone number text box.