Help with Excel 2010

[Digital_Terror]

A spreadsheet updated by several people where I work needs to have the claim # field validated while data is being entered since it is critical to being able to look up those claims later.

There are 3 possible valid ways to enter a claim #:
First is 11 digits long, starting with an R, such as R1234567890
The 2nd and 3rd ways work for the same style claim #'s, which is 12 total digits long, starting with a 1, followed by enough 0's to make 12 total digits, such as 100001234567.

The 12 digit claim # could be entered in it's entirety by the user, or they could just enter the 1234567 portion, and the validation script needs to recognize both methods and then format the cell properly.

Here's the code I'm having troubles with:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False

'Proper Case
If Not Intersect(Target, [Last_Name:First_Name]) Is Nothing Then
Target = WorksheetFunction.Proper(Target)
End If
If Not Intersect(Target, [Claim_Number]) Is Nothing Then
Select Case Target
' Check for old legacy style claim #
Case UCase(Left(Target, 1)) = "R"
If Len(Target) <> 11 Then
MsgBox ("Claim #'s starting with R must be 11 total digits.")
Else: Target = UCase(Target)
End If
' Verify valid claim # and format properly
Case (IsNumeric(Target) And Len(Target) < 12)
Target.NumberFormat = "100000000000"
Case (IsNumeric(Target) And Len(Target) = 12)
If Left(Target, 1) <> "1" Then
MsgBox ("Invalid claim #")
End If
Case Else
MsgBox ("Invalid claim #")
End Select
End If

Application.EnableEvents = True
End Sub

Note that Claim_Number is the named column range in the spreadsheet.
The portion that formats first and last names as proper case is working just fine.

The problem I am having here:
If I enter a claim # such as 'r123' it SHOULD tell me that claim #'s starting with an R must be 11 total digits...instead it just jumps straight to the Case Else message.
If I then clear the cell so that no data is in it at all, THEN it gives me the message about R claim numbers.
If I enter a valid 12 digit style claim # but use fewer than 12 digits (1234567), it formats it properly as it should...but then also tells me it is an invalid claim #.
If i enter the entire 12 digit style claim # (100001234567) it formats it so there is an extra 1 at the beginning (1100001234567) and again displays invalid claim #.

So, in effect, it seems to be executing every case option instead of just the single case option that should apply.

What am I doing wrong here?

Thanks for the assistance, this is driving me nuts!

 

[cyclone3d]

So in a CASE statement, you are checking against the value of the varaible or whatever right after the Select Case statement.

In your case, you are checking against the value of Target.

With all cases, you are comparing Target to a boolean.

You really should just be using a nested IF ELSE statement here.

This should work: (I have not tested this)

If(UCase(Left(target, 1)) = "R") Then
   If(Len(Target) <> 11) Then
      MsgBox ("Claim #'s starting with R must be 11 total digits.")
   Else
      Target = UCase(Target)
   End If
Else If((IsNumeric(Target) AndAlso Len(Target) < 12)) Then
   Target.NumberFormat = "100000000000"
Else If((IsNumeric(Target) AndAlso Len(Target) = 12)) Then
   If (Left(Target, 1) <> "1") Then
      MsgBox ("Invalid claim #")
   End If   
Else
   MsgBox ("Invalid claim #")
End If

 

[Digital_Terror]

Worked for most of the problem, though I did have to redo a few of the statements to work in Excel 2010 (it didn't like AndAlso mainly).

Still had the problem with it using the numberformat for claim #'s less than 12 digits when entering the full 12 digit number, such that it stuck an extra 1 at the front. Fixed that by adding a 2nd numberformat command of "000000000000" to that section.

Anyone know why it would be utilizing the numberformat from the len < 12 section when it should be skipping past that if the length = 12 or more?

Updated code chunk that now works, if anyone is interested:
Yeah, I know I stuck a select case back in there, mostly because I find it easier to read.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False

'Proper Case for First and Last Name fields
If Not Intersect(Target, [Last_Name:First_Name]) Is Nothing Then
    Target = WorksheetFunction.Proper(Target)
End If

'Validate claim #
If Not Intersect(Target, [Claim_Number]) Is Nothing Then
    If (UCase(Left(Target, 1)) = "R") Then
        If (Len(Target) <> 11) Then
            MsgBox ("Claim #'s starting with R must be 11 total digits.")
        Else
            Target = UCase(Target)
        End If
    ElseIf (IsNumeric(Target)) Then
        Select Case (Len(Target))
            Case Is < 12
                Target.NumberFormat = "100000000000"
            Case 12
                If (Left(Target, 1) <> 1) Then
                    MsgBox ("Invalid claim #")
                Else: Target.NumberFormat = "000000000000"
                End If
            Case Else
                MsgBox ("Invalid claim #")
        End Select
    Else
        MsgBox ("Invalid claim #")
    End If
End If

Application.EnableEvents = True
End Sub

Thank you very much for your help, Cyclone3d. Your suggestion helped me get my own thinking straightened out sooner than I would have without it.

 

[cyclone3d]

Glad I could help.

I thought of editing my original reply to first check the IsNumeric by itself. The way you are now doing it is better than what I posted.

I wasn't sure about the AndAlso statement, but that is what I came up with VB. Those multiple comparisons get foggy between different languages so I end up having to look stuff like that up.