Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel: Automatic Conditioning of Text Cell Entries 3

Status
Not open for further replies.

sten28

Technical User
Apr 9, 2007
20
AU
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?
 




Hi,

Copy this code and paste into the SHEET OBJECT Code Window...
Code:
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]
[glasses] [red][/red]
[tongue][/sub]
 
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
 



VB has no such function. However, Excel does...
Code:
    Target.Value = Application.Proper(Target.Value)

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Brilliant! I was getting scared for a moment when I noticed VB only supported UCase and LCase!

Thanks heaps!

Sten :)
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top