InputBox: Force Consistent Entry Format

  • 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

  • Re: Inputbox Data Entry Format


    How about:

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


  • Re: Force Consistent Entry Format


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



  • 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

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!