Adding to exsisting MACRO

[RoadKing]

Rick:
Thanks for the info however, I am not a programmer and would appreciate a
little help. The following is a macro I created. I would like to include
the "proper" code into the this.

Thanks in advance

John

Sub PageFormat()
'
' PageFormat Macro
' Macro recorded 8/19/2007 by John Donadio
'
' Keyboard Shortcut: Ctrl+z
'
Cells.Select
With Selection.Font
.Name = "Times New Roman"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2").Select
End Sub




From: "Rick Rothstein (MVP - VB)" <[email protected]>
Subject: Re: Help- Changing Case
Date: Sunday, August 19, 2007 12:53 PM

Select the cells you want to make Proper and run this small macro:

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

Or, using "pure" VBA code...

For Each R In Selection
R.Value = StrConv(R.Value, vbProperCase)
Next

Rick

 

[Guest]

I'd replace

Cells.Select
With Selection.Font

with

With Cells.Font

I try to avoid select unless absolutely necessary.

HTH,
Barb Reinhardt

 

[David McRitchie]

Often you would write macros as general as possible and make your selection
*before* you run the macro, which might be to select column B before
running the
macro to make changes to existing text strings within that selection to
Proper Case and
with the understanding that the macro will change the cell formatting for
the
entire worksheet. You need to word your request a lot more carefully so
that
someone knows what you want, and to include a summary of that in your
subject.
Don't expect that by including two macros others are going to know what you
want.

How to ask a question, Daniel Petri.
http://support.microsoft.com/kb/555375

Taking what you had you might insert the following just after the last
"End With"
would be as follows,

Dim rng As Range, cell As Range
On Error Resume Next 'In case no cells in selection
Set rng = Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
For Each cell In rng
cell.Formula = StrConv(cell.Formula, vbProperCase)
Next cell
On Error GoTo 0 'Resume normal error handling
Range("A2").Select
End Sub

and good programming practice would have you place the
DIM statement near the beginning of the entire macro. You can
do formatting all at once, but you must change a cell to Proper Case
one cell at a time so you need the loop.

Having reduced the range to text cell constants it isn't really necessary
to
work with the formulas, but it does add an extra layer of protection
against your converting formulas to values.

Take a look at my page
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

So that you would have a macro that make changes later to any selection
that you make before you invoke the macro.




HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm