software

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)) amp; LCase( _
Mid(sStr, 2))
End If
ErrHandler:
Application.EnableEvents = True
End SubMany thanks,
Dan.--
Voodoodan
------------------------------------------------------------------------
Voodoodan's Profile: www.excelforum.com/member.php...nfoamp;userid=597
View this thread: www.excelforum.com/showthread...hreadid=505301How 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 gt; 1 Then Exit Sub
If Intersect(Target, Me.Range(quot;c:cquot;)) 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 lt;gt; Target.Value Then
Application.EnableEvents = False
Target.Value = sStr
End If

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

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

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: www.excelforum.com/member.php...nfoamp;userid=597
View this thread: www.excelforum.com/showthread...hreadid=505301But 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

quot;Voodoodanquot; gt; wrote
in message ...
gt;
gt; Thanks very much for your contributions.
gt;
gt; I have used Dave's version, which got to me through email first, and it
gt; works perfectly!
gt;
gt; Thanks again,
gt; Dan.
gt;
gt;
gt; --
gt; Voodoodan
gt; ------------------------------------------------------------------------
gt; Voodoodan's Profile:
gt; www.excelforum.com/member.php...nfoamp;userid=597
gt; View this thread: www.excelforum.com/showthread...hreadid=505301
gt;

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: www.excelforum.com/member.php...nfoamp;userid=597
View this thread: www.excelforum.com/showthread...hreadid=505301Just 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 lt;bggt;)

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

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

--

Dave Peterson