Hi:
I got frustrated with the change case problem and I've been reading the
mails as a beginner. Your reply sounds like speed dial compared to the
others.
But what does put it in your personal .xls file mean?
Carrie

"Don Guillett" wrote:

try putting this in your personal.xls to use anytime needed.

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub


--
Don Guillett
SalesAid Software

"fjls" wrote in message
...
I have a large spreadsheet and the text is all in uppercase. How would I
apply the formula for proper case to this data?

I assume that it works for you. Instead of putting in each workbook you use
you can put in your personal.xls file. Do windowunhide. If you have it then
touch the cancel buttonuse alt f11 to view codefind personal.xls and put
in a module. You can even assign a custom button to your toolbar if you use
often.

--
Don Guillett
SalesAid Software

"date formats morph the dates/chang case"
rosoft.com wrote in
message ...
Hi:
I got frustrated with the change case problem and I've been reading the
mails as a beginner. Your reply sounds like speed dial compared to the
others.
But what does put it in your personal .xls file mean?
Carrie

"Don Guillett" wrote:

try putting this in your personal.xls to use anytime needed.

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper",
"Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub


--
Don Guillett
SalesAid Software

"fjls" wrote in message
...
I have a large spreadsheet and the text is all in uppercase. How
would I
apply the formula for proper case to this data?

Carrie

Personal.xls is created the first time you record a Macro using Macro
Recorder.

It is used for making macros available to all open workbooks.

ToolsMacroRecord New Macro. A dialog box will come up asking you name the
macro and where to place it. Pick Personal Macro Workbook from the dropdown.
Copy and paste a couple of cells then Stop Recording.

You now have a Personal.xls in your Office\XLSTART folder. You can go to
Visual Basic Editor(Alt+F11) to view the macro you just recorded in a Module.

You can add more macros by recording or by typing/copying them into the
Module.

In this case, copy Don's code into the module.

You can do a FileSave from there or better yet hit ALT + Q to return to the
Excel window.

Then with Personal.xls active, hit WindowHide.

When you close Excel you will be asked if you want to save Personal.xls. Yes!

It will open hidden next time you start Excel.

NOTE: when assigning macros to buttons or menu items you will have to precede
the macro name with Personal.xls.

i.e. Personal.xls!macroname

An alternative to Personal.xls is to create an add-in(*.xla) with your macros
in it and load it through ToolsAdd-ins.

The benefit of this is that you don't have to precede the macro name with the
filename.

A disadvantage is that you will not see the macros in the ToolsMacroMacros
dialog.


Gord Dibben Excel MVP

On Sun, 17 Apr 2005 17:54:02 -0700, date formats morph the dates/chang case
rosoft.com wrote:

Hi:
I got frustrated with the change case problem and I've been reading the
mails as a beginner. Your reply sounds like speed dial compared to the
others.
But what does put it in your personal .xls file mean?
Carrie

"Don Guillett" wrote:

try putting this in your personal.xls to use anytime needed.

Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub


--
Don Guillett
SalesAid Software

"fjls" wrote in message
...
I have a large spreadsheet and the text is all in uppercase. How would I
apply the formula for proper case to this data?