Word gives userd the ability to change the case of text to UPPER, lower &
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

try

=Proper(a1)

"Capitalization in Excel" wrote:

Word gives userd the ability to change the case of text to UPPER, lower &
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

You can use helper cells with the function PROPER

i.e. =PROPER(A1) entered in B1

To do a great whack at one go without a helper column you need VBA

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub



Gord Dibben MS Excel MVP


On Fri, 8 Aug 2008 12:12:01 -0700, Capitalization in Excel <Capitalization
in Excel @discussions.microsoft.com wrote:

Word gives userd the ability to change the case of text to UPPER, lower &
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

Just to add to Gord's posting...

You would use what Gord posted to fix any **existing** text already on your
worksheet. To make sure any future entries on the worksheet are in proper
case, you will need this worksheet Change event code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub

As written, it applies to the entire worksheet but it can be restricted to
smaller ranges if need be. To install the code, right click the tab of the
worksheet you want this to apply to, select View Code from the popup menu
that appears and copy/paste the above code into the code window that
appeared.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can use helper cells with the function PROPER

i.e. =PROPER(A1) entered in B1

To do a great whack at one go without a helper column you need VBA

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub



Gord Dibben MS Excel MVP


On Fri, 8 Aug 2008 12:12:01 -0700, Capitalization in Excel <Capitalization
in Excel @discussions.microsoft.com wrote:

Word gives userd the ability to change the case of text to UPPER, lower &
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

hi,

And just to add to Ricks' post I wouldn't do this if there are any formula
on your sheet because they will be converted to values.

You could modify it thus

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Target.HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"Rick Rothstein (MVP - VB)" wrote:

Just to add to Gord's posting...

You would use what Gord posted to fix any **existing** text already on your
worksheet. To make sure any future entries on the worksheet are in proper
case, you will need this worksheet Change event code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub

As written, it applies to the entire worksheet but it can be restricted to
smaller ranges if need be. To install the code, right click the tab of the
worksheet you want this to apply to, select View Code from the popup menu
that appears and copy/paste the above code into the code window that
appeared.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can use helper cells with the function PROPER

i.e. =PROPER(A1) entered in B1

To do a great whack at one go without a helper column you need VBA

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub



Gord Dibben MS Excel MVP


On Fri, 8 Aug 2008 12:12:01 -0700, Capitalization in Excel <Capitalization
in Excel @discussions.microsoft.com wrote:

Word gives userd the ability to change the case of text to UPPER, lower &
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

I should have said if you enter any NEW formula in your sheet they will be
converted to values.

"Mike H" wrote:

hi,

And just to add to Ricks' post I wouldn't do this if there are any formula
on your sheet because they will be converted to values.

You could modify it thus

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Target.HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"Rick Rothstein (MVP - VB)" wrote:

Just to add to Gord's posting...

You would use what Gord posted to fix any **existing** text already on your
worksheet. To make sure any future entries on the worksheet are in proper
case, you will need this worksheet Change event code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub

As written, it applies to the entire worksheet but it can be restricted to
smaller ranges if need be. To install the code, right click the tab of the
worksheet you want this to apply to, select View Code from the popup menu
that appears and copy/paste the above code into the code window that
appeared.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can use helper cells with the function PROPER

i.e. =PROPER(A1) entered in B1

To do a great whack at one go without a helper column you need VBA

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub



Gord Dibben MS Excel MVP


On Fri, 8 Aug 2008 12:12:01 -0700, Capitalization in Excel <Capitalization
in Excel @discussions.microsoft.com wrote:

Word gives userd the ability to change the case of text to UPPER, lower &
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

Thanks Mike (I think you have corrected me on this before... I don't know
why I have such a blind spot to it).

Rick


"Mike H" wrote in message
...
hi,

And just to add to Ricks' post I wouldn't do this if there are any formula
on your sheet because they will be converted to values.

You could modify it thus

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Target.HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"Rick Rothstein (MVP - VB)" wrote:

Just to add to Gord's posting...

You would use what Gord posted to fix any **existing** text already on
your
worksheet. To make sure any future entries on the worksheet are in proper
case, you will need this worksheet Change event code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub

As written, it applies to the entire worksheet but it can be restricted
to
smaller ranges if need be. To install the code, right click the tab of
the
worksheet you want this to apply to, select View Code from the popup menu
that appears and copy/paste the above code into the code window that
appeared.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can use helper cells with the function PROPER

i.e. =PROPER(A1) entered in B1

To do a great whack at one go without a helper column you need VBA

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub



Gord Dibben MS Excel MVP


On Fri, 8 Aug 2008 12:12:01 -0700, Capitalization in Excel
<Capitalization
in Excel @discussions.microsoft.com wrote:

Word gives userd the ability to change the case of text to UPPER, lower
&
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

Or to this..........

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Formula = StrConv(Target.Formula, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub


Gord

On Fri, 8 Aug 2008 17:45:19 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Thanks Mike (I think you have corrected me on this before... I don't know
why I have such a blind spot to it).

Rick


"Mike H" wrote in message
...
hi,

And just to add to Ricks' post I wouldn't do this if there are any formula
on your sheet because they will be converted to values.

You could modify it thus

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Target.HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

"Rick Rothstein (MVP - VB)" wrote:

Just to add to Gord's posting...

You would use what Gord posted to fix any **existing** text already on
your
worksheet. To make sure any future entries on the worksheet are in proper
case, you will need this worksheet Change event code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub

As written, it applies to the entire worksheet but it can be restricted
to
smaller ranges if need be. To install the code, right click the tab of
the
worksheet you want this to apply to, select View Code from the popup menu
that appears and copy/paste the above code into the code window that
appeared.

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
You can use helper cells with the function PROPER

i.e. =PROPER(A1) entered in B1

To do a great whack at one go without a helper column you need VBA

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub



Gord Dibben MS Excel MVP


On Fri, 8 Aug 2008 12:12:01 -0700, Capitalization in Excel
<Capitalization
in Excel @discussions.microsoft.com wrote:

Word gives userd the ability to change the case of text to UPPER, lower
&
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?