InputBox letter and number checking

[tags23]

Hello, i've tried for a while to make an inputbox check for numbers and letters to be in specific places of the entered value, but got stuck here. FYI not very familiar with VBA.
Basically i'm in need to check if the inputbox received a value like this -> [number][number][number][UCaseLetter][UCaseLetter][UCaseLetter][number][number][number] for example 000XYZ000

Currently i have something like this, because i took a code from here and modified it a bit, but it's not quite right.

Sub InputBoxChecker()


Dim i As Integer
Dim id As String 'pw is the password
Dim blnGetID As Boolean
Dim isValid As Boolean
Dim msg As String
Dim ans As Integer
Dim firstThree As String
Dim midThree As String
Dim lastThree As String


'Ascii codes: 48-57 = 0 To 9, 65-90 = A To Z, 97-122 = a To z
blnGetID = True


Do While blnGetID = True


    '//Get ID
    id = InputBox("Enter a valid value", "Value form")
        
        If id = "" Then
            isValid = False
            Exit Do '//no value, Cancelled
            
        ElseIf Len(id) <> 9 Then
            msg = "Inputbox has to be 9 characters."
            isValid = False '//Not nine characters in length
        
        Else
            isValid = True
            msg = "The value entered is wrong" '//default message
            i = 1
            Do While isValid = True And i < 4  [B]// the looping is the issue here i guess, because i couldn't figure out how to do it.[/B]
firstThree = Left(id, 3)
isValid = True
If (Asc(firstThree) > 57 And Asc(firstThree) < 48) Then
            isValid = Flase
            Else
midThree = Mid(id, 4, 3)
isValid = True
If (Asc(midThree) > 90 And Asc(midThree) < 65) Then
            isValid = Flase
            Else
lastThree = Right(id, 3)
isValid = True
If (Asc(lastThree) > 57 And Asc(lastThree) < 48) Then
            isValid = Flase
End If
End If
End If
                
                i = i + 1
            Loop
        
        End If


    '//If password invalid, ask user if another attempt is desired
    If isValid = False Then
        msg = msg & vbNewLine & "Try again?"
        ans = MsgBox(msg, vbYesNo)
        If ans = vbYes Then
            blnGetID = True
        Else
            blnGetID = False
        End If
    End If


Loop


End Sub

Thanks in advance!

 

[mumps]

Give this a try:

Sub InputBoxChecker()
    Dim RegEx As Object, Match As Object
    Dim id As String
    id = InputBox("Enter a valid value", "Value form")
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = False
        .Pattern = "[0-9][0-9][0-9][A-Z][A-Z][A-Z][0-9][0-9][0-9]"
    End With
    Set Match = RegEx.Execute(id)
    If Match.Count <> 1 Then
        MsgBox "Invalid ID.  Please try again and enter in this format: 111AAA222."
    Else
        MsgBox ("Valid ID.")
    End If
End Sub

 

[Rick Rothstein]

Give this a try:

Sub InputBoxChecker()
    Dim RegEx As Object, Match As Object
    Dim id As String
    id = InputBox("Enter a valid value", "Value form")
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .MultiLine = False
        .Global = True
        .IgnoreCase = False
        .Pattern = "[0-9][0-9][0-9][A-Z][A-Z][A-Z][0-9][0-9][0-9]"
    End With
    Set Match = RegEx.Execute(id)
    If Match.Count <> 1 Then
        MsgBox "Invalid ID.  Please try again and enter in this format: 111AAA222."
    Else
        MsgBox ("Valid ID.")
    End If
End Sub

There is no need to call out the "big guns" for this problem... instead of using a Regular Expression engine, VB's Like operator is more than up to the task...

[table="width: 500"]
[tr]
	[td]Sub InputBoxChecker()
  Dim ID As String
  ID = InputBox("Enter a valid value", "Value form")
  If ID Like "###[A-Z][A-Z][A-Z]###" Then
    MsgBox ("Valid ID.")
  Else
    MsgBox "Invalid ID.  Please try again and enter in this format: 111AAA222."
  End If
End Sub[/td]
[/tr]
[/table]

 

[tags23]

Thanks to both of you! Everything's working for me as i wanted it now.

 

[mumps]

Glad to help.