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.

Sounds like you need data validation:
http://www.contextures.com/xlDataVal01.html
http://www.contextures.com/xlDataVal08.html

Regards,
Ryan---

--
RyGuy


"jnf40" wrote:

> 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.
>

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" <(E-Mail Removed)> wrote in message
news:E77E3B4F-8CDE-4403-842A-(E-Mail Removed)...
>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.
>

Thanks but I would really like to do it using code

"ryguy7272" wrote:

> Sounds like you need data validation:
> http://www.contextures.com/xlDataVal01.html
> http://www.contextures.com/xlDataVal08.html
>
> Regards,
> Ryan---
>
> --
> RyGuy
>
>
> "jnf40" wrote:
>
> > 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.
> >

Damn!! That is pretty awesome!!


--
RyGuy


"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" <(E-Mail Removed)> wrote in message
> news:E77E3B4F-8CDE-4403-842A-(E-Mail Removed)...
> >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.
> >
>
>

Thank you for your very kind comment... it is much appreciated.

Rick


"ryguy7272" <(E-Mail Removed)> wrote in message
news:49E9EC68-30B7-4FB2-8727-(E-Mail Removed)...
> Damn!! That is pretty awesome!!
>
>
> --
> RyGuy
>
>
> "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" <(E-Mail Removed)> wrote in message
>> news:E77E3B4F-8CDE-4403-842A-(E-Mail Removed)...
>> >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.
>> >
>>
>>

Well, I guess I wasn't as kind as I should have been. I was VERY impressed
though...could you tell...

I knew Excel could handle some data validations tasks, but I thought it was
quite feeble compared to what Access is capable of. I now have to reconsider
my assumptions of Excel!! I've been a heavy user of Excel for about five
years now. Every day I am learning new stuff, and I am constantly amazed at
the things this tool is capable of!!

I tip my hat to you Rick!!
Well done!!

--
RyGuy


"Rick Rothstein (MVP - VB)" wrote:

> Thank you for your very kind comment... it is much appreciated.
>
> Rick
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:49E9EC68-30B7-4FB2-8727-(E-Mail Removed)...
> > Damn!! That is pretty awesome!!
> >
> >
> > --
> > RyGuy
> >
> >
> > "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" <(E-Mail Removed)> wrote in message
> >> news:E77E3B4F-8CDE-4403-842A-(E-Mail Removed)...
> >> >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.
> >> >
> >>
> >>
>
>

See inline comments...

> Well, I guess I wasn't as kind as I should have been. I was VERY
> impressed
> though...could you tell...

No, I must have missed that. <g>

> I knew Excel could handle some data validations tasks, but I thought it
> was
> quite feeble compared to what Access is capable of. I now have to
> reconsider
> my assumptions of Excel!!

I've been programming since 1981 (mostly in various forms of Basic, but also
Fortran, C, and several UNIX scripting languages) and have found that, with
a sufficient amount of effort, there is very little you cannot make code do
for you, even in "limited" languages.

> I've been a heavy user of Excel for about five years now.

Me, I have been a light user of Excel for about a year now (I'm retired, so
I have no jobs to use it on); however, I have a fairly long track record
with VBA's sibling... the compiled version of Visual Basic. I find that I
can apply a lot of what I know from compiled VB to the Excel VBA world; the
biggest stumbling block for me being a lack of intimate familiarity with
Excel's Object Model.

> Every day I am learning new stuff,

Me too!

> and I am constantly amazed at the things this tool is capable of!!

Me too!

> I tip my hat to you Rick!!
> Well done!!

And, once again, I thank you for your most generous comments.

Rick

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" <(E-Mail Removed)> wrote in message
> news:E77E3B4F-8CDE-4403-842A-(E-Mail Removed)...
> >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.
> >
>
>

I have to step out for a little while, so I'll look at your second question
when I get back; however, I think I don't like the change you made to my
posted code. Are you still running it in the Change event (hard to tell for
sure as you left the event head out of your message)? If so, then your For
Each loop is just wasting time. The Change event reacts to the cell that has
just been changed. For what you want to do, that cell's content is the only
one that the event procedure should be looking at. Theoretically, the other
cells you are checking with your For-Each loop were already handled when
they were entered... there is no reason to check them again... they won't
have changed. IF they did change, then the Change event code I initially
posted would handle them then-and-there. You can sort of think of the Change
event as a giant For-Each loop if you want... for each cell (within the
specified range) that gets changed, the event code is executed against that
cell (where the Target argument is Set by the system to refer to the cell
being changed). As I said, I'll look at your second question later on today
(but it does, at first glance, look like it can be shortened considerably).

Rick


"jnf40" <(E-Mail Removed)> wrote in message
newsCB6A3FE-3F12-434B-84E6-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E77E3B4F-8CDE-4403-842A-(E-Mail Removed)...
>> >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.
>> >
>>
>>