Data Validation in Userform, Checking String is Numeric and Seven Digits Long

[vba_monkey]

Hello,

I am trying to validate entries into a userform field to check that it only contains numbers and is 7 digits long. All i need it to do is highlight the field in red if the entry is not correct but not if the field is exited without entering anything. This is what I have so far:

Private Sub SJNKE_Policy_1_TB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
     If Not IsNumeric(SJNKE_Policy_1_TB.Text) And Len(SJNKE_Policy_1_TB.Text) <> 7 Or SJNKE_Policy_1_TB.Text <> "" Then
        SJNKE_Policy_1_TB.BackColor = &HFF&
     Else
        SJNKE_Policy_1_TB.BackColor = &H80000005
     End If
End Sub

The results from this are:

If non numeric characters are included the field turns red, which is correct.
If a number less than or more than 7 digits is entered the field stays white, which is wrong.
If the field is activated but then exited without entering anything the field turns red, which is wrong.

Any ideas please?

 

[MickG]

Slightly different to requirements , but try this:-

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If -CLng(Chr(KeyAscii) Like "[0-9]") = 0 Then
    MsgBox "Please Enter Numbers Only"
    KeyAscii = KeyAscii * -CLng(Chr(KeyAscii) Like "[0-9]")
  ElseIf Len(TextBox1.Text) = 7 Then
    KeyAscii = 0
  End If
End Sub

 

[Rick Rothstein]

Hello,

I am trying to validate entries into a userform field to check that it only contains numbers and is 7 digits long. All i need it to do is highlight the field in red if the entry is not correct but not if the field is exited without entering anything. This is what I have so far:

Private Sub SJNKE_Policy_1_TB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
     If Not IsNumeric(SJNKE_Policy_1_TB.Text) And Len(SJNKE_Policy_1_TB.Text) <> 7 Or SJNKE_Policy_1_TB.Text <> "" Then
        SJNKE_Policy_1_TB.BackColor = &HFF&
     Else
        SJNKE_Policy_1_TB.BackColor = &H80000005
     End If
End Sub

The results from this are:

If non numeric characters are included the field turns red, which is correct.
If a number less than or more than 7 digits is entered the field stays white, which is wrong.
If the field is activated but then exited without entering anything the field turns red, which is wrong.

See if this replacement for what you posted does what you want...

[table="width: 500"]
[tr]
	[td]Private Sub SJNKE_Policy_1_TB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Len(SJNKE_Policy_1_TB.Text) = 0 Or SJNKE_Policy_1_TB.Text Like "#######" Then
    SJNKE_Policy_1_TB.BackColor = &H80000005
  Else
    SJNKE_Policy_1_TB.BackColor = &HFF&
  End If
End Sub[/td]
[/tr]
[/table]

 

[vba_monkey]

That does the trick, thanks Rick.