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.
pdberger wrote:
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