Solved: Data Validation on User Form

[jamieCR9]

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]

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]

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]

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]

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.

[SamT]

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]

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]

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]

I wish I could find a way to feed your babies, but thanks, much appreciated. (Appreciation can't feed babies either, I know...)