InputBox: Force Consistent Entry Format

DISCARNET · Nov 18th 2006

hi
I am using an input box to enter a vehicle registration. how can i format it so that it always the same regardles of how it was typed in
for instance ABCD 123 is typed in by one person but another may type it in as ABCD123.
It needs to populate a cell in the main sheet in the format of 4 space 3 (as in british number plates) so it can verify the vehicle against another spreadsheet.
also needs to always be in capital letters

I'm guessing left$ or right$ would do this but have never used this funtion and my last few hours atempting to has failed

there are over 140 vehicles in the data base so data validation is to long
also due to a tied in yard managment screen its not possible to enter the vehicles in both formats

many thanks
Tim

StefanG · Nov 18th 2006

Re: Inputbox Data Entry Format

Hi DISCARNET,

How about:

Code

vAnswer = Application.InputBox ...


Range("PlateNumbers") = Format(vAnswer, "@@@@ @@@")
Range("PlateNumbers").Value = UCase(Range("PlateNumbers"))

Stefan

Dave Hawley · Nov 19th 2006

Re: Inputbox Data Entry Format

See Also Collect User Information in Excel

rbrhodes · Nov 19th 2006

Re: Force Consistent Entry Format

DISCARNET,

Examples of using Left and Right, UCase to convert to caps and concatentating strings with some basic checking.

Code

Sub simple()


Dim num As String


    'Get input (in caps)
    num = UCase(InputBox("Enter license number. Format: ABCD 123"))
    
    'Check length of string
    If Len(num) = 7 Then
        'If 7 chars then insert space
        num = Left(num, 4) & " " & Right(num, 3)
    End If


End Sub

Display More

Code

Sub lessSimple()


Dim lth As Long
Dim num As String


    'Get input (in caps)
    num = UCase(InputBox("Enter license number Format: ABCD 123"))


    'Check for position 5 = space
    If InStr(num, " ") = 5 Then
        Exit Sub
    End If
    
    'No space at character position 5, get length of string
    lth = Len(num)
        
    'Check length
    Select Case lth
        Case Is = 7
            'First 4 chars <space> last 3 chars
            num = Left(num, 4) & " " & Right(num, 3)
        Case Is = 6
            'First 4 chars <space> last 2 chars
            num = Left(num, 4) & " " & Right(num, 2)
        Case Is = 5
            'First 4 chars <space> last 1 char
            num = Left(num, 4) & " " & Right(num, 1)
        Case Is > 7
            'Either correct or too long, do nothing?
    End Select


End Sub

Display More

Cheers,

dr

DISCARNET · Nov 19th 2006

Re: InputBox: Force Consistent Entry Format

Thanks guys
Little busy this week but going to try your methods this week sometime,
they look good, will let you all know how i get on

Thank You All
Tim

DISCARNET · Nov 27th 2006

Re: InputBox: Force Consistent Entry Format

Thanks rbrhodes
your less simple option works perfect
added a line to check if the vehicle was a trailer or rigid
also managed to manipulate it to reconise the older style number plates as well

StefanG your option worked, but if someone entered data as 1234 abc it would add an extra space so it could not lookup the vehicle on the database

Thanks Guys

InputBox: Force Consistent Entry Format

Participate now!

Share