View Full Version : Solved: Data Validation on User Form
jamieCR9
03-28-2010, 09:45 AM
Is it possible to validate data in a text box beyond maximum length? Because I have got a field for phone number for example, and would like that if someone enters text that the data can't be accepted and a msgbox comes up telling them they have to change it. Same for surname, I would like that only text can be entered. There are more data fields, but those two examples cover all really. Thanks for the help.
Bob Phillips
03-28-2010, 11:07 AM
Maybe this will work for you.
First create a class module, name it FormEventSink, and add this code
Option Explicit
Private Const mmModule As String = "FormEventManager"
Public WithEvents mNumberGroup As MSForms.TextBox
Public WithEvents mTextGroup As MSForms.TextBox
Public EnableEvents As Boolean
Private mMaxWhole As Long
Private mMaxLetters As Long
Private mMinLetters As Long
Public Property Let MaxWholeDigits(ByVal pMaxWholeDigits As Long)
mMaxWhole = pMaxWholeDigits
End Property
Public Property Let MaxLetters(ByVal pMaxLetters As Long)
mMaxLetters = pMaxLetters
End Property
Public Property Let MinLetters(ByVal pMinLetters As Long)
mMinLetters = pMinLetters
End Property
'----------------------------------------------------------------------
Private Sub mNumberGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'----------------------------------------------------------------------
Const mpProcedure As String = "mNumberGroup_KeyPress"
With mNumberGroup
If .Text & Chr(KeyAscii) Like "*[!0-9]" Or _
.Text & Chr(KeyAscii) Like String$(mMaxWhole + 1, "#") Then
Beep
KeyAscii = 0
End If
End With
End Sub
'----------------------------------------------------------------------
Private Sub mTextGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'----------------------------------------------------------------------
Const mpProcedure As String = "mTextGroup_KeyPress"
With mTextGroup
If .Text & Chr(KeyAscii) Like "*[0-9.]*" Or _
Len(.Text & Chr(KeyAscii)) > mMaxLetters Then
Beep
KeyAscii = 0
End If
End With
End Sub
Then in the form activate, add
Option Explicit
Private mcInputSink As Collection
Private Sub UserForm_Activate()
Dim mpClass As FormEventManager
With Me
Set mcInputSink = New Collection
Set mpClass = New FormEventManager
Set mpClass.mNumberGroup = .txtPhoneNumber
mpClass.MaxWholeDigits = 7
mcInputSink.Add mpClass
Set mpClass = New FormEventManager
Set mpClass.mTextGroup = .txtName
mpClass.MaxLetters = 25
mcInputSink.Add mpClass
End With
End Sub
jamieCR9
03-28-2010, 01:26 PM
Okay I'll show you my spreadsheet and you can tell me whether it works or not, I'm not that advanced to adapt code or anything. Thanks mate. :)
The validation is for all the the textbox fields, on the main userform.
Bob Phillips
03-28-2010, 02:44 PM
So you want me to put validation on all of those textboxes? What about credit card checkdigit checks, valid postcodes, etc?
Do I get a free ARsenal season ticket for this?
jamieCR9
03-28-2010, 03:03 PM
So you want me to put validation on all of those textboxes? What about credit card checkdigit checks, valid postcodes, etc?
Do I get a free ARsenal season ticket for this?
Well not sure about Arsenal season ticket, but gratitude of the highest level ;) I will list what I need assuming you are up for it:
Surname - has to be text and maximum 20 characters
Forename - has to be text and maximum 20 characters
Address - any input (as need a number and text), max. 30 characters
City - has to be text and max. 20 characters
Postcode - any input, max. 8 characters
Phone Number - has to be numerical, has to be 11 digits
Credit card number - has to be numerical, has to be 16 digits
Security number - has to be numerical, 3 digits
Date - any input, 5 characters (XX/XX format if possible?)Thanks a billion if possible, also if they enter wrong data can message boxes come up and prompt them to change it.
Jamie,
He's given you all you need.
Just copy his examples and change the Names and Max values as needed.
Or...
Find two Arsenal season tickets :devil2:
jamieCR9
03-28-2010, 03:31 PM
Well I think he implied that he was going to have a crack at my actual excel, but he can clarify that. It's that I'm terrible at this.
Bob Phillips
03-28-2010, 03:53 PM
Gratitude won't feed my babies!
In this, most of the validation is done on input, so you can't enter a number on the names, you can't input more than 16 digits for the credit card and so on, but there is a final check to see that all details have been supplied and that minimum numbers have been met.
jamieCR9
03-28-2010, 04:13 PM
I wish I could find a way to feed your babies, but thanks, much appreciated. (Appreciation can't feed babies either, I know...)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.