InputBox letter and number checking

tags23

New Member
Joined
Dec 25, 2017
Messages
2
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.

Code:
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!
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,111
Give this a try:
Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
Give this a try:
Code:
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...
Code:
[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]
 
Last edited:

tags23

New Member
Joined
Dec 25, 2017
Messages
2
Thanks to both of you! Everything's working for me as i wanted it now. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top