3 letter capitalisation

[Voodoodan]

Hiya,

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

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

So, if I enter 'daniel', it returns 'Daniel'. If I enter 'dan' i
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

 

[Dave Peterson]

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

 

[Bernie Deitrick]

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

 

[Voodoodan]

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.

 

[Bernard Liengme]

But what happens if I type May or Amy? Do these have to be capitalized even
when they are not contractions?

 

[Voodoodan]

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.

 

[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]

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]

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