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

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

Cliff Edwards

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

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

I never did like that before...

Thanks!
Cliff Edwards

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

--
Gary''s Student - gsnu200790


"WembleyBear" wrote:

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

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