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. |
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 |
All times are GMT +1. The time now is 01:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com