Excel: Automatic Conditioning of Text Cell Entries 3

[sten28]

It's been suggested I ask here to see if anyone can help:

To be brief, I'm using Microsoft Excel 2003 and am trying to achieve the following:

* a user enters text into a cell

* Excel will automatically format the cell into Title Case

Simple what I want to achieve, but it's proving harder than I thought it'd be! Any advice?

Also, does anyone believe this can be achieved without VB?

 

[SkipVought]

Hi,

Copy this code and paste into the SHEET OBJECT Code Window...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        Target.Value = UCase(Target.Value)
    End If
End Sub

Obviously, the RANGE will need to be your intended range.

Also this code anticipates single cell entries, not copy 'n' paste.

Skip,
[sub]
[red][/red]
[/sub]

 

[sten28]

Thanks a ton!

One last question though - is it possible to format the text as Title Case (Mr Joe Bloggs) instead of UPPERCASE (MR JOE BLOGGS)?

Cheers,

Sten

 

[SkipVought]

VB has no such function. However, Excel does...

    Target.Value = Application.Proper(Target.Value)

Skip,
[sub]
[red][/red]
[/sub]

 

[sten28]

Brilliant! I was getting scared for a moment when I noticed VB only supported UCase and LCase!

Thanks heaps!

Sten

 

[PHV]

VB has no such function
Really ?
What about this ?
Target.Value = StrConv(Target.Value, vbProperCase)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Learn sumthin' new! ==> * ;-)

Skip,
[sub]
[red][/red]
[/sub]