Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping on Data Entry
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping on Data Entry
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
while deleting rows it finds an error - error trapping | Excel Programming | |||
Error Trapping when getting external Data | Excel Programming | |||
Error Trapping | Excel Programming | |||
Trapping Data Input with Curser Keys or Entry Key | Excel Programming | |||
trapping error | Excel Programming |