text boxes (TextBox) are among the controls used in the creation of the UserForm. They are suitable for data visualization, but they are often used to gather input. In this regard it is worth specifying that the data that are defendants in the TextBox are text or string data. Problems arise when you want to enter different data types, such as dates or numbers. In these cases it is always good contralateral placing no validation of the data.
propose an example that uses regular expressions to check if the data was written correctly. The check is done and Exit event uses a generic routine that is passed to the pattern. I preferred writing to alert you in a Label control any error message, rather than using annoying error messages.
< N Then
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' for example, inserting a new
'UserForm (UserForm1) containing:
' a Label (Label1) and three TextBox
'(TextBox1, TextBox2, Textbox3) 
' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'other path can be found at: ' xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ' http://excelvba.altervista.org/blog/index.php/Excel-VBA/Espressioni-Regolari-e-Pattern-applicazione-Form.html
' Paste all the code in the form
'class of
userform Private Sub UserForm_Initialize () With Me
. Caption = "Sample TextBox with validation
. Label1.Caption =" "
. Label1.ForeColor = & HFF &
. TextBox1.Tag = "European Data" . TextBox2.Tag = "Tax Code"
. TextBox3.Tag = "Decimal" . TextBox1.Text = "12/10/2009" . TextBox2 . Text = "DVXJHT61B12Z600F"
.TextBox3.Text = "2,5" End With End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'data europea
UserForm_Controllo_Event_Exit Me.Label1, _
Me.TextBox1, _
Cancel, _
"^(3[01][12]\d0?[1-9])/(0?[13578]1012)/" & _
"(\d{2}(192021)\d{2})$" & _
"^(30[12]\d0?[1-9])/(0?[469]11)/(\d{2}" & _
"(192021)\d{2})$" & _
"^(2[0-8][01]\d0?[1-9])/(0?2)/(\d{2}" & _
(192,021) \\ d {2}) $ "& _
" ^ 29 / (0? 2) / (200000) $ "& _
" ^ 29 / (0? 2) / (192 021) ? (0 [48] [2468] [048] "& _
[13579] [26]) $"
End Sub Private Sub
TextBox2_Exit (ByVal Cancel As MSForms.ReturnBoolean)
'tax code
UserForm_Controllo_Event_Exit Me . Label1,
Me.TextBox2 _, _
Cancel,
_ "^ ([AZ] {6})" & _
"(((\\ d {2}) [ACELMRT] (3 [01] [12 ] \\ d0 [1-9] "& _
" 7071 [56] \\ d4 [1-9])) "& _
" ((\\ d {2}) [DHPS] (30 [12] \\ d0 [1-9] 70 " & _
"[56]\d4[1-9]))" & _
"((\d{2})B(2[0-8]1\d0[1-9]6[0-8]5\d4[1-9]))" & _
"((0[048][2468][048][13579][26])B(2969)))" & _
"([A-Z]{1})([0-9L-NPQ-V]{3})" & _
"([A-Z]{1})$"
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'numero a virgola mobile
UserForm_Controllo_Event_Exit Me.Label1, _
Me.TextBox3, _
Cancel, _
"^(0[+-]?" & _
"((?!0)\d+([,]\d+)?" & _
"[0]+([,]\d+)?))$"
End Sub
Sub UserForm_Controllo_Event_Exit( _
ByRef oLabel As MSForms.Label, _
ByRef oControl As MSForms.Control, _
ByRef Cancel As MSForms.ReturnBoolean, _
Optional ByVal sPattern As String = "[\w\s]+")
'______________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'Di Roberto Mensa nick r
'______________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'Effettua un controllo sul testo digitato in un
'controllo, se il controllo ha esito negativo
'ripassa cancel=true impedendo l'uscita del focus
'e seleziona l'intero testo digitato
'da richiamare dall'evento exit di controlli che
'hanno cancel come argomento
'oLabel è il controllo Label entro cui comunicare
'il messaggio di errore
Dim re As Object
Static sLabelCaption As String
Static bCancel As Boolean
If bCancel = False Then
sLabelCaption = oLabel.Caption
End If
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.ignorecase = True
With oControl
If Len(.Text) > 0 Then
If re.test(.Text) = False Then
oLabel.Caption = _
"Testo " & .Tag & " - Non valido!"
.SelStart = 0
.SelLength = Len(.Text)
Cancel = True
bCancel = True
End If
End If
If End With Cancel = False Then
bCancel = False
oLabel.Caption = sLabelCaption
End If End Sub
0 comments:
Post a Comment