Simple VBA Select Case statement not working, Why not?

5.2k Views Asked by At

Simple Select Case statement is not working as expected. Message should show this as falling into the "too long" bucket but it comes up as "too short"

Lenn = 20
    Select Case Lenn
    Case 7
        msg = "You've entered " & Lenn & " digits, this is ok"
    Case 11
        msg = "You've entered " & Lenn & " digits, is ok"
    Case 12
        msg = "You've entered " & Lenn & " digits, this is ok"
    Case 13
        msg = "You've entered " & Lenn & " digits, this is ok"
    Case 17
        msg = "You've entered " & Lenn & " digits, this is ok"
    Case Is < 7
        msg = "Hmm…. The number you entered is too short. Can you try again?"
    Case Is > 17
        msg = "Hmm…. The number you entered is too long. Can you try again?"
    Case Is = 8, 9, 10, 14, 15, 16
        msg = "Hmm…. The number you entered is not the right length. Can you try again?"
    Case Else
        msg = "Hmm…. The number you entered is not the right length. Can you try again?"
    End Select
1

There are 1 best solutions below

2
On BEST ANSWER

It will enter the "too short" portion of your case statement if Lenn is a string. Try forcing it to be an integer before assigning it by adding this to the top of your code:

Dim Lenn As Integer