Changing Text Case

[Jan :\)]

Hi all, Excel 2007:

How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to change
the text from Upper case to Proper case in B4:B15, but, no matter where I
place the cursor, or how I try to get the text to change in those cells, it
does not does not change the text in those cells. If I select the range and
then select the PROPER in the Text list, it puts the function in the first
cell of the column, but, does not change the case of the text in those
cells. So, what am I doing wrong? How would I do this for a range of
several columns?

Jan

 

[Gary''s Student]

Select the cells you want to convert and run this macro:

Sub fixit()
For Each r In Selection
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next
End Sub

 

[Jan :\)]

Select the cells you want to convert and run this macro:

Sub fixit()
For Each r In Selection
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next
End Sub

Thanks, Gary. I take it there is no means to do this as a built-in function
in Excel? The reason I ask is that I was asked to show some of my
co-workers how to do this using a built-in function, like the Change Case or
Change Text Case function in Word if there was one. But, I'll see if I can
create a Macro they can add to their Quick toolbar.

Thank you for your time and help.

Jan

 

[Jan :\)]

Hi Gary....

That did work ok fine. What Value would I use other than the r to be able
to create a Macro for the Upper and Lower as well?

Jan

 

[Jan :\)]

Is there a way to create a Macro that I can use for all workbooks? I know I
can copy them to another workbook, but, would like to be able to run the
Macros when needed in other workbooks as well.

Jan

 

[Jan :\)]

Note that the student's macro will wipe out formulas if there are any in
the
range you selected.

These won't.

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


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


Gord Dibben MS Excel MVP

Thank you very much, Gord. It is good that it will not delete any forumlas.

Is there a way that a Master Macro for each of these can be created that can
be Run with any workbook? Or, is that what the code is indicating?

Jan

 

[Jan :\)]

Place the code in your Personal.xls and it will be available for all
workbooks.

Or download Chip Pearson's add-in.

http://www.cpearson.com/excel/Download.htm

Scroll down to Case Convert

Ahhh...thank you very much. Disregard my other question on this to your
other reply. I did not see this reply at the time. I'll try the download
as well.

I truly do appreciate your time and help.

Jan

 

[Gord Dibben]

Glad to help.


Gord

Ahhh...thank you very much. Disregard my other question on this to your
other reply. I did not see this reply at the time. I'll try the download
as well.

I truly do appreciate your time and help.

Jan

 

[Jan :\)]

One other thing, Gord....you mentioned that another form of code would
delete any formulas in the cells when they the text case was changed...will
Chip's add-in also delete any formulas? It did not say in the description
of the add-in if it would or not. I just want to be sure before I use it on
such cells, or recommend it to others.

Jan

 

[Gord Dibben]

Chip's code will not wipe formulas.

His code operates on the selection and includes the following line to ignore
cells with formulas.

For Each Rng In AllCells.SpecialCells(xlCellTypeConstants, xlTextValues)


Gord

 

[Jan :\)]

Chip's code will not wipe formulas.

His code operates on the selection and includes the following line to
ignore
cells with formulas.

For Each Rng In AllCells.SpecialCells(xlCellTypeConstants, xlTextValues)

Thank you very much fro the confirmation.

Jan