CODEWITHSUNDEEP
excelvba
Max Bridge
Max Bridge

Reputation: 331

How to validate a cell value in excel VBA

I need a macro that validate a cell value. It needs to check if the value if the value is 7 letters and 6 digits(passwds123456). The letters will always be the same but not the 6 numbers at the end.

I hope I'm clear enough. This is the code I currently have:

Private Sub CommandButton15_Click()

        Dim i As Integer

        If Len(Range("H3").Value) <> 13 Then

            MsgBox "ok lenght is not  13"

        End If

        For i = 8 To 13

            If IsNumeric(i) = True Then

               MsgBox "6 last are numbers"

            End If
        Next i
End Sub

but this doesn't correctly detect the numbers part.

What am I missing?

Upvotes: 1

Views: 9806

Answers (1)

dwirony
dwirony

Reputation: 5450

Instead of testing each character to see if it's a number, use Right to test the last 6 characters as a single string (using IsNumeric):

Option Explicit
Sub test()

If Len(Range("H3").Value) <> 13 Then
    MsgBox "ok length is not 13"
Else
    If IsNumeric(Right(Range("H3").Value, 6)) = True Then
        MsgBox "6 last are numbers"
    End If
End If

End Sub

To expand, if your value in H3 is "passwds123456", Right(Range("H3").Value, 6) will return "123456", which then returns True against IsNumeric

Example

EDIT:

As @YowE3K pointed out, a number in another notation could pass this test, so a cleaner solution (with a bit more to it) would be to test each of the characters individually:

Option Explicit
Sub test()

Dim i As Integer, flag As Boolean

If Len(Range("H3").Value) <> 13 Then
    MsgBox "ok length is not 13"
Else
    For i = 0 To 5
        If Not IsNumeric(Mid(Range("H3").Value, 8 + i, 1)) = True Then
            flag = True
        End If
    Next i
    If flag = False Then
        MsgBox "6 last are numbers"
    End If
End If

End Sub

Upvotes: 4

Related Questions