Use of Proper Case

[WembleyBear]

Hi

Easy question I hope. I have one cell (E11) on my spreadsheet, which I need
to be converted to Proper Case after input for all words in vba, for various
reasons I don't want to use the worksheet function for this. Can anyone tell
me the code I should use please?

Many Thanks
Martyn

Excel 2000, Windows 2003 server over Citrix PS4

 

[ward376]

Range("e11").Value = Application.Proper(Range("e11").Value)

Cliff Edwards

 

[Gary''s Student]

Sub properfy()
Set r = Range("E11")
s = r.Value
wrds = Split(s, " ")
For i = 0 To UBound(wrds)
v = wrds(i)
v = UCase(Left(v, 1)) & Right(v, Len(v) - 1)
wrds(i) = v
Next
r.Value = Join(wrds, " ")
End Sub



will convert:
now is the time for all good men
into:
Now Is The Time For All Good Men

 

[Rick Rothstein \(MVP - VB\)]

Range("e11").Value = Application.Proper(Range("e11").Value)

Or, avoiding the call out to the worksheet function...

Range("E11").Value = StrConv(Range("E11").Value, vbProperCase)

Rick

 

[Otto Moehrbach]

You should use a Worksheet_Change event macro like the following. Note that
this macro must be placed in the sheet module of the sheet in question. To
access that module, right-click on the sheet tab, select View Code, and
paste this macro into that module. "X" out of the module to return to your
sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("E11")) Is Nothing Then
Application.EnableEvents = False
Target.Value = Application.Proper(Target.Value)
Application.EnableEvents = True
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Note to Martyn,

Otto posted a more complete answer as to how to implement what you want to
do; however, like Cliff, he also suggested calling out to the worksheet to
use its PROPER function... I would still suggest you use the StrConv
function statement call I posted in its place (but definitely use the
structure he posted).

Rick

 

[ward376]

I never did like that before...

Thanks!
Cliff Edwards

 

[Gary Keramidas]

i found there was a difference in conversion of some text using the different
methods, but i can't for the life of me remember what the circumstances were.
maybe i can hunt through some old apps and stimulate my brain.<g>

 

[WembleyBear]

Thanks Rick

That did the trick, though I can see the value of Otto's suggestion too.


Martyn