Proper Case with .

[graham gordon]

Hi

I have managed to put this code together thanks
to "Pearson Software " websight but does anyone have
an idea on how I can add a (.) *thats period, after the first upper
case.

J. Smith




Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("E4:E300, F4:F300")) Is
Nothing Then
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
End If
Application.EnableEvents = True
End Sub


thanks in advance
Graham

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Graham,

Do you mean that 'job smith' becomes 'J.Smith'. If so, then try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("E4:E300, F4:F300")) Is Nothing Then
With Target(1)
.Value = UCase(Left(.Value, 1)) & "." & _
StrConv(Right(.Value, Len(.Value) - 1),
vbProperCase)
End With
End If
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Just for consideration:

given
sStr = "ABCDEFGHIJK"

Bob's formula:

? UCase(Left(sStr, 1)) & "." & StrConv(Right(sStr, Len(sStr) -
1),vbProperCase)

Gives:
A.Bcdefghijk


My guess was you actually want:
? UCase(Left(sStr, 1)) & "." & Right(strConv(sStr,vbProperCase), Len(sStr) -
1)

which Gives:
A.bcdefghijk