Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to add some validation to a VBA sub and I need to ensure the user enters a number and that it is no longer than the set limit, I'm pulling this in using a function as it varies depending on the team.

I can get the textlength

With Selection.Validation
    .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="0", Formula2:=ValidMaxLength

But obviously this can allow text.

Any help much appreciated.

share|improve this question
    
Is it possible to convert the number to text (string) and then check for its length? –  pbies Jan 21 at 16:15

2 Answers 2

Yes, throw in an isnumeric and a length:

If IsNumeric(Range("A1")) Then

    If Len(Range("A1").Value) < 3 Then

        'do stuff

    End If

End If

It's probably also best to check that it's not empty first, among other things. Since you only gave us a snippet, I'm only addressing a snippet.

share|improve this answer

Len will not work here as it could pick up 1.0 as 3 digits and 100 as 3 digits. Since it is a number you'll simply need to check to see if the value is less than a certain amount (and likely more than 0 depending on your needs). Something like the code below should get you started.

Sub ValidateEntry()
    Dim rngToValidate As Range
    Dim val_UpperLimit As Double
    Dim val_LowerLimit As Double
    Set rngToValidate = Range("A1")
    val_UpperLimit = 100
    val_LowerLimit = 0

    If IsNumeric(rngToValidate) _
    And rngToValidate.Value < val_UpperLimit _
    And rngToValidate.Value > val_LowerLimit Then
        'passed validation
    Else
        'failed validation
    End If
End Sub
share|improve this answer
1  
This is entirely true. If you don't want to count characters and only want to count digits, you'll need something that doesn't use len. –  Raystafarian Jan 22 at 14:12
    
OP stated digits. Hence my response. And even if it was characters, since we are dealing with numbers, it is more reliable to validate within a range (unless you need to limit the numbers after the decimal point). –  guitarthrower Jan 22 at 17:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.