• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Excel VBA Input Box - data (string) valdiation

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
    Do
        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")
    Else
        ValidateName = True
  End If
End Function

Open in new window

0
stmoritz
Asked:
stmoritz
  • 2
  • 2
2 Solutions
 
rspahitzCommented:
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
    Else
        ' 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
        Next
    End If
    
    ValidateName = bValid
End Function

Open in new window

0
 
stmoritzAuthor Commented:
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.
0
 
rspahitzCommented:
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)
0
 
stmoritzAuthor Commented:
excellent. thank you very much!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now