Validate User Entry

Question

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

L42 · Answer 1 · 2014-01-15T22:12:33.927

2

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.

edited Jan 15 '14 at 22:12

answered Jan 15 '14 at 01:20

L42

19,427
11
44
68

1

Looks great, but the only this is : not a number doesn't mean a letter. – Bernard Saucier Jan 15 '14 at 01:21
Wow, when I saw your correction I wondered if your regular expression would work (didn't think it needed a comma in there) so I tried it out and it didn't (terrible copy-paste; missed the first line), while it actually does. So I went ahead and finished typing my answer out xD. +1 for beating me to the punch. Twice. – Bernard Saucier Jan 15 '14 at 01:48
@BernardSaucier haha you're approach is good as well. and also pointing out the details on ASCII but not very well versed with that so i resort to reg ex. Thanks for pointing out the flaw in my first solution :D and thanks for correcting the `comma`. it shouldn't be there. :D – L42 Jan 15 '14 at 01:51
2

You shouldn't have changed `[a-zA-Z]` because it has a different meaning than `[A-z]` (which includes those random characters separating upper case and lower case characters). – Bernard Saucier Jan 15 '14 at 14:56

Bernard Saucier · Accepted Answer · 2014-01-15T15:22:26.240

1

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.

edited Jan 15 '14 at 15:22

answered Jan 15 '14 at 01:41

Bernard Saucier

2,240
1
19
28

Hello BernardSaucier thank you for the comment. I am still having a problem where I can still enter a number and a letter and the macro will run. For example, I can input "C1" and it will run. Do you have any suggestion on how to fix this. Thanks! – Jan 15 '14 at 15:11
Updated answer, go ahead and try it! – Bernard Saucier Jan 15 '14 at 15:23

Validate User Entry

2 Answers2

Linked