3 letter capitalisation

Hiya,

I've modified a bit of code, below, so that if someone types anything into cell C25, then it capitalises the first letter.

Is there any way of modifying it a little bit more so that, as well as the above, if someone types in a 3-letter word then it capitalises all three letters?

So, if I enter 'daniel', it returns 'Daniel'. If I enter 'dan' it returns 'DAN'.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo ErrHandler
If Target.Count = 1 And Target.Column = 3 Then
Application.EnableEvents = False
sStr = Target.Value
Target.Value = UCase(Left(sStr, 1)) & LCase( _
Mid(sStr, 2))
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Many thanks,
Dan.

How about:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myConversion As Long
Dim sStr As String

On Error GoTo ErrHandler

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c:c")) Is Nothing Then Exit Sub

Select Case Len(Target.Value)
Case Is = 3: myConversion = vbUpperCase
Case Else: myConversion = vbProperCase
End Select

sStr = StrConv(Target.Value, myConversion)

If sStr <> Target.Value Then
Application.EnableEvents = False
Target.Value = sStr
End If

ErrHandler:
Application.EnableEvents = True
End Sub


Voodoodan wrote:
>
> Hiya,
>
> I've modified a bit of code, below, so that if someone types anything
> into cell C25, then it capitalises the first letter.
>
> Is there any way of modifying it a little bit more so that, as well as
> the above, if someone types in a 3-letter word then it capitalises all
> three letters?
>
> So, if I enter 'daniel', it returns 'Daniel'. If I enter 'dan' it
> returns 'DAN'.
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>
> On Error GoTo ErrHandler
> If Target.Count = 1 And Target.Column = 3 Then
> Application.EnableEvents = False
> sStr = Target.Value
> Target.Value = UCase(Left(sStr, 1)) & LCase( _
> Mid(sStr, 2))
> End If
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
> Many thanks,
> Dan.
>
> --
> Voodoodan
> ------------------------------------------------------------------------
> Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
> View this thread: http://www.excelforum.com/showthread...hreadid=505301

--

Dave Peterson

Dan,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sStr As String

On Error GoTo ErrHandler
If Target.Count = 1 And Target.Column = 3 Then
Application.EnableEvents = False
sStr = Target.Value
If Len(sStr) = 3 Then
Target.Value = UCase(sStr)
Else
Target.Value = UCase(Left(sStr, 1)) & LCase( _
Mid(sStr, 2))
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

--
HTH,
Bernie
MS Excel MVP


"Voodoodan" <[email protected]> wrote in message
news:[email protected]...
>
> Hiya,
>
> I've modified a bit of code, below, so that if someone types anything
> into cell C25, then it capitalises the first letter.
>
> Is there any way of modifying it a little bit more so that, as well as
> the above, if someone types in a 3-letter word then it capitalises all
> three letters?
>
> So, if I enter 'daniel', it returns 'Daniel'. If I enter 'dan' it
> returns 'DAN'.
>
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>
> On Error GoTo ErrHandler
> If Target.Count = 1 And Target.Column = 3 Then
> Application.EnableEvents = False
> sStr = Target.Value
> Target.Value = UCase(Left(sStr, 1)) & LCase( _
> Mid(sStr, 2))
> End If
> ErrHandler:
> Application.EnableEvents = True
> End Sub
>
>
> Many thanks,
> Dan.
>
>
> --
> Voodoodan
> ------------------------------------------------------------------------
> Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
> View this thread: http://www.excelforum.com/showthread...hreadid=505301
>

Thanks very much for your contributions.

I have used Dave's version, which got to me through email first, and it works perfectly!

Thanks again,
Dan.

But what happens if I type May or Amy? Do these have to be capitalized even
when they are not contractions?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Voodoodan" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks very much for your contributions.
>
> I have used Dave's version, which got to me through email first, and it
> works perfectly!
>
> Thanks again,
> Dan.
>
>
> --
> Voodoodan
> ------------------------------------------------------------------------
> Voodoodan's Profile:
> http://www.excelforum.com/member.php...nfo&userid=597
> View this thread: http://www.excelforum.com/showthread...hreadid=505301
>

Just curious, did you change the code so it looked like:

Select Case Len(Target.Value)
Case 2 To 5: myConversion = vbUpperCase
Case Else: myConversion = vbProperCase
End Select

(just checking on you <bg>)

Voodoodan wrote:
>
> The data being entered is specialised, so there'll be no need to type in
> any general, everyday names, as such.
>
> I've actually modified it a little now to capitalise all letters within
> 2-5 letters. This is because when people enter these words they will be
> acronyms of a certain team/unit. However, anything over 5 letters will
> likely be a team/unit's name in full, so it just needs the first letter
> to be capitalised.
>
> Dan.
>
> --
> Voodoodan
> ------------------------------------------------------------------------
> Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
> View this thread: http://www.excelforum.com/showthread...hreadid=505301

--

Dave Peterson

Close, I did it this way, however I prefer the neatness of your line!

Select Case Len(Target.Value)
Case Is = 2, 3, 4, 5: myConversion = vbUpperCase
Case Else: myConversion = vbProperCase
End Select

Dan.

Originally Posted by Dave Peterson

Just curious, did you change the code so it looked like:

Select Case Len(Target.Value)
Case 2 To 5: myConversion = vbUpperCase
Case Else: myConversion = vbProperCase
End Select

(just checking on you <bg>)

Voodoodan wrote:
>
> The data being entered is specialised, so there'll be no need to type in
> any general, everyday names, as such.
>
> I've actually modified it a little now to capitalise all letters within
> 2-5 letters. This is because when people enter these words they will be
> acronyms of a certain team/unit. However, anything over 5 letters will
> likely be a team/unit's name in full, so it just needs the first letter
> to be capitalised.
>
> Dan.
>
> --
> Voodoodan
> ------------------------------------------------------------------------
> Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
> View this thread: http://www.excelforum.com/showthread...hreadid=505301

--

Dave Peterson

Putting all the numbers is a nice way if you wanted to skip one of them. But
way too much work if you were checking up to (say) 32000 characters <vvbg>.



Voodoodan wrote:
>
> Close, I did it this way, however I prefer the neatness of your line!
>
> Select Case Len(Target.Value)
> Case Is = 2, 3, 4, 5: myConversion = vbUpperCase
> Case Else: myConversion = vbProperCase
> End Select
>
> Dan.
>
> Dave Peterson Wrote:
> > Just curious, did you change the code so it looked like:
> >
> > Select Case Len(Target.Value)
> > Case 2 To 5: myConversion = vbUpperCase
> > Case Else: myConversion = vbProperCase
> > End Select
> >
> > (just checking on you <bg>)
> >
> > Voodoodan wrote:
> > >
> > > The data being entered is specialised, so there'll be no need to type
> > in
> > > any general, everyday names, as such.
> > >
> > > I've actually modified it a little now to capitalise all letters
> > within
> > > 2-5 letters. This is because when people enter these words they will
> > be
> > > acronyms of a certain team/unit. However, anything over 5 letters
> > will
> > > likely be a team/unit's name in full, so it just needs the first
> > letter
> > > to be capitalised.
> > >
> > > Dan.
> > >
> > > --
> > > Voodoodan
> > >
> > ------------------------------------------------------------------------
> > > Voodoodan's Profile:
> > http://www.excelforum.com/member.php...nfo&userid=597
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=505301
> >
> > --
> >
> > Dave Peterson
>
> --
> Voodoodan
> ------------------------------------------------------------------------
> Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
> View this thread: http://www.excelforum.com/showthread...hreadid=505301

--

Dave Peterson