Trying to set up PROPER fx

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

what have you tried? Post code, etc


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Cherith Cutestory" <dkohn@insightbb.com> wrote in message
news:l191g.903339$x96.692795@attbi_s72...
> 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

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

Cherith Cutestory wrote:
>
> 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

--

Dave Peterson

THanks for the help


Dave Peterson wrote:
> 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
>
> Cherith Cutestory wrote:
>> 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
>