CODEWITHSUNDEEP
excelvalidationvba
user3170862
user3170862

Reputation:

Validate User Entry

I am trying to run a marco that check the cells users have entered and makes sure they are not blank. I am having a hard time with one cell. I want the user to be limited to entering only 2 letters and I want it to check to make sure no numbers were entered in that cell otherwise throw up an error message and exit sub. Any help is greatly appreciated!

If Worksheets("New PN").Range("B12").Value = "" Then
MsgBox "Date cannot be left empty.", vbOKOnly + vbExclamation, "Entry Error"
Exit Sub
End If

Upvotes: 0

Views: 68

Answers (2)

L42
L42

Reputation: 19727

Try this:

my_string = Worksheets("New PN").Range("B12").Value

If Len(my_string) = 2 And Left(my_string, 1) Like "[A-Za-z]" _
    And Right(my_string, 1) Like "[A-Za-z]" Then
    '~~execute what you want here
Else
    MsgBox "Invalid input" & vbNewLine & "Enter 2 LETTERS only"
End If

Hope this helps.

Upvotes: 2

Bernard Saucier
Bernard Saucier

Reputation: 2270

Try THIS! 

cellContent = Worksheets("New PN").Range("B12").Value
leftCC = Left(cellContent, 1)
rightCC = Right(cellContent, 1)
If Len(cellContent) <> 2 Then

        MsgBox "There needs to be 2 characters."
        Exit Sub

ElseIf (Asc(leftCC) < 65 Or Asc(leftCC) > 122 _
         Or (Asc(leftCC) > 90 And Asc(leftCC) < 97)) _
    Or _
       (Asc(rightCC) < 65 Or Asc(rightCC) > 122 _
         Or (Asc(rightCC) > 90 And Asc(rightCC) < 97)) Then

        MsgBox "Both characters can only be letters."
        Exit Sub

End If

Might be big and scary, but it will get the job done 100%.

EDIT : The Asc(char) formula returns the ascii number for the character supplied. The outer limits of a-z and A-Z are 65 and 122, however some non-characters are included in the middle (namely [, \, ], ^, _, `). Hence the horrid if.

Upvotes: 1

Related Questions