Can I change case automatically without using PROPER function?
 

Can I change the case in cells without using the PROPER function. What i mean
by that is if I type - 'the happy dog' in a cell I want it to appear as 'The
Happy Dog' automatically WITHOUT having to use the PROPER function.

Can I change case automatically without using PROPER function?
 

John,

You can use the worksheet change event. Right click your sheet tab, view
code and paste this in

As written it applies to the entire sheet. If you want to limit this to a
specific range then remove the comment marks from the if-end if and set yopu
range as required.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
'If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
'End If
End Sub

Mike

"John" wrote:

Can I change the case in cells without using the PROPER function. What i mean
by that is if I type - 'the happy dog' in a cell I want it to appear as 'The
Happy Dog' automatically WITHOUT having to use the PROPER function.

Can I change case automatically without using PROPER function?
 

You can use an event macro to translate your typing. Say we want input in
column A to be automatically translated:

Private Sub Worksheet_Change(ByVal Target As Range)
Set T = Target
Set A = Range("A:A")
If Intersect(T, A) Is Nothing Then Exit Sub
Application.EnableEvents = False
T.Value = Application.WorksheetFunction.Proper(T.Value)
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200809


"John" wrote:

Can I change the case in cells without using the PROPER function. What i mean
by that is if I type - 'the happy dog' in a cell I want it to appear as 'The
Happy Dog' automatically WITHOUT having to use the PROPER function.

Can I change case automatically without using PROPER function?
 

A small change maybe in case the OP enters a formula

T.Value = Application.WorksheetFunction.Proper(T.Formula)

Mike

"Gary''s Student" wrote:

You can use an event macro to translate your typing. Say we want input in
column A to be automatically translated:

Private Sub Worksheet_Change(ByVal Target As Range)
Set T = Target
Set A = Range("A:A")
If Intersect(T, A) Is Nothing Then Exit Sub
Application.EnableEvents = False
T.Value = Application.WorksheetFunction.Proper(T.Value)
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200809


"John" wrote:

Can I change the case in cells without using the PROPER function. What i mean
by that is if I type - 'the happy dog' in a cell I want it to appear as 'The
Happy Dog' automatically WITHOUT having to use the PROPER function.

Can I change case automatically without using PROPER function?
 

Thanks Mike. I'll put your suggestion in my toolbox.
--
Gary''s Student - gsnu200809


"Mike H" wrote:

A small change maybe in case the OP enters a formula

T.Value = Application.WorksheetFunction.Proper(T.Formula)

Mike

"Gary''s Student" wrote:

You can use an event macro to translate your typing. Say we want input in
column A to be automatically translated:

Private Sub Worksheet_Change(ByVal Target As Range)
Set T = Target
Set A = Range("A:A")
If Intersect(T, A) Is Nothing Then Exit Sub
Application.EnableEvents = False
T.Value = Application.WorksheetFunction.Proper(T.Value)
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200809


"John" wrote:

Can I change the case in cells without using the PROPER function. What i mean
by that is if I type - 'the happy dog' in a cell I want it to appear as 'The
Happy Dog' automatically WITHOUT having to use the PROPER function.