More help needed with letters and numbers as a license plate

  • [INDENT]The Coding Assistants provided by B Rama Krishna was great and is really appreciated.
    i am enclosing a copy of the excel project with some license plate examples. While the dash is random, it dose not comply with some state formats like Connecticut, Texas, Canada, Pennsylvania, New Jersey also its not working on every cell I can not figure it out. More help is needed i am starting to think what my employer is asking is even possible. There is also some data on sheet 2 you will also see that the dash is appearing in the cells were the state abbreviations is placed.[/INDENT]

    [VBA]
    Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, Cell As Range, strFormat As String
    Dim i As Integer
    On Error GoTo ErrorHandler:
    If Not Intersect(Target, Range("C35:R57")) Is Nothing And Target.Value <> "" Then 'change the range as required
    Application.EnableEvents = False

    For Each Cell In Target
    With Cell
    strFormat = "@@@@" & String(Len(.Value) - 2, "@")
    .Value = UCase(Format(.Value, strFormat))
    i = WorksheetFunction.RandBetween(2, Len(.Value) - 1)
    .Value = Left(.Value, i) & "-" & Right(.Value, Len(.Value) - i)
    End With
    Next Cell
    Application.EnableEvents = True
    End If
    Exit Sub
    ErrorHandler:
    MsgBox "Error Number:" & Err.Number & vbCrLf & _
    "Error Description:" & Err.Description & vbCrLf & _
    "Error at: " & Cell.Address
    Application.EnableEvents = True


    [/VBA]

  • Hi,


    1. The problem with dash appearing in the in the cells for the state abbreviations (assuming the abbreviation is contains only two letters) is sorted out. Also information entered (upto 2 letters) is converted into upper case.


    2 Is there a norm for the dash to appear depending on the state ? If so, pl indicate against each state in Col C of sheet 1 so that it can be incorporated


    3. Dash not appearing in cells happens if the application.EnableEvents is set to false (like exiting the sub routine midway - which can happen during debug mode). Also pl make sure that there are no blank spaces already before entering any data into a cell as the blank space is treated as a character.


    Pl check the attached file and give information on point 2 so that the same can be incorporated

  • Thank you so much for all your help on this project Mr.Krishna We really appreciate the additional states added by you. I am enclosing the sample sheet with the actual position of the dash per state, it's not actually a dash it there state logo or map but a dash will be just as good for our project. The code was removed from the sheet to show what the employer wants.

  • Sorry about that I am enclosing the another sample sheet with the actual position of the dash per state, it's not actually a dash it there state logo or map but a dash will be just as good for our project,


    [VBA]


    Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, Cell As Range, strFormat As String
    Dim i As Integer
    On Error GoTo ErrorHandler:
    If Not Intersect(Target, Range("C35:R57")) Is Nothing And Target.Value <> "" Then 'change the range as required
    Application.EnableEvents = False
    For Each Cell In Target
    With Cell
    .Value = WorksheetFunction.Trim(.Value)
    If Len(.Value) > 2 Then
    strFormat = "@@@@" & String(Len(.Value) - 2, "@")
    .Value = UCase(Format(.Value, strFormat))
    i = WorksheetFunction.RandBetween(2, Len(.Value) - 1)
    .Value = Left(.Value, i) & "-" & Right(.Value, Len(.Value) - i)
    Else
    .Value = UCase(.Value)
    End If
    End With
    Next Cell
    Application.EnableEvents = True
    End If
    Exit Sub
    ErrorHandler:
    MsgBox "Error Number:" & Err.Number & vbCrLf & _
    "Error Description:" & Err.Description & vbCrLf & _
    "Error at: " & Cell.Address
    Application.EnableEvents = True
    End Sub
    [/VBA]


    As you can see there are a number of different styles of plates. Personalize plates have no dash in them Some states have 2 letters then numbers. Some do not.

  • Hi,


    1. As you can see, there is no set pattern for the location of the dash based on the state. (eg. 'NJ' - sometimes there is no dash, sometimes it appears any where). Given this scenario, i do not think it is possible to factor that into the programme. Some senior member of this forum may have a better solution..


    2. For state 'AR', the dash appears in 4th position from right (at least in both the instances in your file). I have changed the code to insert the dash in the same position, if the state is chosen as 'AR' in Col F, L, R. You may enter the position against the state in sheet1 Col C. The state needs to be chosen before entering the vehicle number.


    The file is attached.