Trying to set up PROPER fx

C

Cherith Cutestory

I'm trying to set up a column which already has text in it to
automatically format the text to have a capital letter when I type more
items in empty cells, I cannot get this to go, any help

Thanks
 
D

Dave Peterson

You'll need more than formatting--maybe an event macro???

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)

errHandler:
Application.EnableEvents = True
End Sub

This entry:
I converted the typed in value to proper.
would be changed to:
I Converted The Typed In Value To Proper

If you only wanted the first letter of the first word capitalized, then change:
Target.Value = StrConv(Target.Value, vbProperCase)
to
Target.Value = ucase(left(Target.Value,1)) & lcase(mid(target.value,2))

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
C

Cherith Cutestory

THanks for the help


Dave said:
You'll need more than formatting--maybe an event macro???

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)

errHandler:
Application.EnableEvents = True
End Sub

This entry:
I converted the typed in value to proper.
would be changed to:
I Converted The Typed In Value To Proper

If you only wanted the first letter of the first word capitalized, then change:
Target.Value = StrConv(Target.Value, vbProperCase)
to
Target.Value = ucase(left(Target.Value,1)) & lcase(mid(target.value,2))

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...
 
Top