Rick thanks for your help, I tweeked it a little and came up with the
following:
If Intersect(Range("C9:C28,H9:H28,M9:M28"), Target) Is Nothing Then
For Each cell In ActiveSheet.Range("C9:C28,H9:H28,M9:M28")
If cell.Value "" Then
On Error GoTo Whoops
Application.EnableEvents = False
c = Val(Left(cell.Value, Len(cell.Value) - 1)) & _
"-" & UCase(Right(cell.Value, 1))
If c Like "####-[A-Z]" Or c Like "#####-[A-Z]" Then
cell.Value = c
Else
MsgBox "Your entry is not correct!", vbExclamation
cell.Select
End If
End If
Whoops:
Application.EnableEvents = True
Next
End If
again thanks
I tried using your code to also find if an entry for a name might have left
the space out, for example johnsmith and have it give the same msg but it did
not work. Any suggestions? I have the following code which checks for various
things but can't seem to get it to check for a left out space or even to add
a period after a middle initial.
If Intersect(Range("B9:B28,G9:G28,L9:L28"), Target) Is Nothing Then
For Each cell In Range("B9:B28,G9:G28,L9:L28")
If cell.Value "" Then
cell.Formula = StrConv(cell.Formula, vbProperCase)
l = Len(cell.Value)
For i = 1 To l
If Mid(cell.Value, i, 3) = " mc" Or Mid(cell.Value, i, 2) = "Mc" Then
cell.Value = _
Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) &
Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) &
Mid(cell.Value, i + 3, l)
If Mid(cell.Value, i, 4) = " mac" And Mid(cell.Value, i, 5) < " mack" Then
cell.Value = _
Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) &
Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) &
Mid(cell.Value, i + 4, l)
If Mid(cell.Value, i, 3) = "Mac" And Mid(cell.Value, i, 4) < "Mack" Then
cell.Value = _
Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) &
Mid(cell.Value, i + 1, 2) & UCase(Mid(cell.Value, i + 3, 1)) &
Mid(cell.Value, i + 4, l)
If Mid(cell.Value, i, 3) = " o'" Or Mid(cell.Value, i, 2) = "O'" Then
cell.Value = _
Mid(cell.Value, 1, i - 1) & UCase(Mid(cell.Value, i, 1)) &
Mid(cell.Value, i + 1, 1) & UCase(Mid(cell.Value, i + 2, 1)) &
Mid(cell.Value, i + 3, l)
If Mid(cell.Value, i, 8) = "Van Den " Then cell.Value = _
Mid(cell.Value, 1, i - 1) & "van den " & UCase(Mid(cell.Value, i
+ 8, 1)) & Mid(cell.Value, i + 9, l)
If Mid(cell.Value, i, 8) = "Van Der " Then cell.Value = _
Mid(cell.Value, 1, i - 1) & "van der " & UCase(Mid(cell.Value, i
+ 8, 1)) & Mid(cell.Value, i + 9, l)
If Mid(cell.Value, i, 3) = "De " Then cell.Value = _
Mid(cell.Value, 1, i - 1) & "de " & UCase(Mid(cell.Value, i + 3,
1)) & Mid(cell.Value, i + 4, l)
If Mid(cell.Value, i, 3) = "La " Then cell.Value = _
Mid(cell.Value, 1, i - 1) & "la " & UCase(Mid(cell.Value, i + 3,
1)) & Mid(cell.Value, i + 4, l)
If Mid(cell.Value, i, 4) = "Van " Then cell.Value = _
Mid(cell.Value, 1, i - 1) & "van " & UCase(Mid(cell.Value, i + 4,
1)) & Mid(cell.Value, i + 5, l)
If Mid(cell.Value, i, 4) = "Von " Then cell.Value = _
Mid(cell.Value, 1, i - 1) & "von " & UCase(Mid(cell.Value, i + 4,
1)) & Mid(cell.Value, i + 5, l)
If cell.Value = "pool" Or cell.Value = "Pool" Then cell.Value = "POOL"
Next
End If
Next
End If
End If
again thanks!
"Rick Rothstein (MVP -
VB)" wrote:
Put the following event procedure into the code window for the worksheet you
want this functionality on (right click the worksheet tab, select View Code
from the popup menu, copy/paste the code into the code window that
appears)...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answer As String
If Not Intersect(Target, Range("A:A,C:C,E:E")) Is Nothing Then
On Error GoTo Whoops
Application.EnableEvents = False
Answer = Val(Left(Target.Value, Len(Target.Value) - 1)) & _
"-" & UCase(Right(Target.Value, 1))
If Answer Like "####-[A-Z]" Or Answer Like "#####-[A-Z]" Then
Target.Value = Answer
Else
MsgBox "Your entry is not of the proper shape!", vbExclamation
Target.Select
End If
End If
Whoops:
Application.EnableEvents = True
End Sub
You do need to change the column references in the first If-Then statement
from the Range("A:A,C:C,E:E") I used to the 3 columns you want this check
performed on (at least, that is what I think you asked for... if you are
only inputting the Vehicle Number into one column, then replace the 3 column
references with just a single reference for the column you are interested
in).
Rick
"jnf40" wrote in message
...
I have a workbook with 3 columns that have information the user inputs.
Columns C and H have 20 rows from 9 to 28 and Column M has 15 rows from 9
to 23. What I need is when the user enters a Vehicle number, which will be
4
or 5 numbers followed by an alpha character, depending on how they enter
the
number I always want it to format the same way, and then check for
duplicates, for example: if they enter 3442g or 3442G or 3442-g or 3442-G
or
3442 -g or 3442 -G or 3442- g or 3442- G or 3442 - g or 3442 - G etc. it
would always end up showing as 3442-G. The numbers and alpha characters
will
always be different and there could be 5 numbers instead of 4 such as
36857-H. Any and all help is greatly appreciated.