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.


--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: http://www.excelforum.com/member.php...nfo&userid=597
View this thread: http://www.excelforum.com/showthread...hreadid=505301

3 letter capitalisation
 

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

3 letter capitalisation
 

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" wrote in message
...

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

3 letter capitalisation
 

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

3 letter capitalisation
 

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" wrote
in message ...

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

3 letter capitalisation
 

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

3 letter capitalisation
 

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

3 letter capitalisation
 

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

3 letter capitalisation
 

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