Excel VBA Input Box - data (string) valdiation

Posted on 2011-04-29
Last Modified: 2012-05-11
I found this code on the web and would like to amend it (already slightly changed) in order to validate the entered data for ActiveCompany

ActiveCompany should not be larger than 10 letters/numbers and is only allowed to contain letters or numbers, no spaces or symbols, points, etc.

Thanks for your help.
Sub Use()
    Dim ActiveCompany As String
    Dim nameOk As Boolean
    nameOk = True
        ActiveCompany = InputBox("Please enter the designated Code for ", "Code")
        If (ActiveCompany <> "") Then nameOk = ValidateName(ActiveCompany)
    Loop While (nameOk = False) Or (ActiveCompany <> "")
End Sub
Function ValidateName(ActiveCompany As String) As Boolean
    Dim strLength As Integer
    Dim I As Integer
    Dim numSpaces As Integer
    Dim tempString As String
    Dim msb As Integer
    ActiveCompany = Trim(ActiveCompany)
    strLength = Len(ActiveCompany)
    For I = 1 To strLength
        If Left(ActiveCompany, 1) = " " Then
            numSpaces = numSpaces + 1
        End If
        ActiveCompany = Right(userName, Len(userName) - 1)
    Next I
    If (numSpaces <> 1) Then
        ValidateName = False
        msb = MsgBox("Please enter just two names separated by one space", vbCritical, "Error")
        ValidateName = True
  End If
End Function

Open in new window

Question by:stmoritz
    LVL 22

    Accepted Solution

    Are you looking to identify if the ActiveName variable contains these things, or looking for strip them if they're there.

    Based on the ValidateName function, I assume that you simply want to check for these things and report them.

    so change it to this:

    Function ValidateName(ActiveCompany As String) As Boolean
        Dim bValid As Boolean
        Dim iCharCntr As Integer
        Dim strChar As String
        bValid = True ' assume true until proven wrong
        ' validation 1: cannot exceed 10 letters/digits
        If Len(ActiveCompany) > 10 Then
            bValid = False
            ' validation 2: only allowed to contain letters or numbers
            For iCharCntr = 1 To Len(ActiveCompany)
                strChar = Mid(ActiveCompany, iCharCntr, 1)
                Select Case strChar
                    Case "A" To "Z", "a" To "z", "0" To "9"
                        ' this is okay so do nothing
                    Case Else
                        bValid = False
                        Exit For
                End Select
        End If
        ValidateName = bValid
    End Function

    Open in new window


    Author Comment

    You're assumption is right. Check if validation is okay, if not display error message/hint and force a re-enter of the code.

    However, I seem to be in an endless loop, no matter what I enter even things that should work for example XFH57 or APPLE...  any help appreciated.
    LVL 22

    Assisted Solution

    It seems that your loop is not quite right...try this instead:

    Sub Use()
       Loop While (nameOk = False) And (ActiveCompany <> "")
    End Sub

    You were checking for an "Or" condition rather than "And", meaning that if the condition was bad or something was entered (<> "") then ask again.
    Instead you should ask again if the entry is bad (and not omitted)

    Author Closing Comment

    excellent. thank you very much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now