Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper Case with .
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper Case with .
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) "graham gordon" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Proper Case with .
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 -- Regards, Tom Ogilvy Bob Phillips wrote in message ... 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) "graham gordon" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change from upper case to proper case in excel 2002 | Excel Discussion (Misc queries) | |||
Proper Case | Excel Discussion (Misc queries) | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
Excel: How do I change all upper case ss to proper case? | Excel Worksheet Functions | |||
Changing Upper case to Proper Case | Excel Worksheet Functions |