Macro
 

I recieved a reply to this earlier asking if I really
wanted a1:ax1 and I do but I think it got me out of the
loop to receive an answer so I am reposting this. Any
help will be greatly appreciated.

I am trying to write a macro that allows the user to pick
a font and then applies it to certain parts of the
worksheet.

Using the macro below, everything works fine except that
it is only changing the fonts on the column and row
headers. i.e.: The A, B, C etc that defines the column
or likewise the 1, 2, 3 etc that defines the row.

I think I'm close but cant figure out what I'm doing
wrong. Any help will be greatly appreciated.


ChangeFont Macro
' Macro recorded 05/12/2004 by Cindy Jordan
'
Dim dlgAnswer As Boolean
Dim objFontChoice As Font
Sheets("MedRates").Select
Range("a1").Select
dlgAnswer = Application.Dialogs(xlDialogFont).Show
If dlgAnswer = False Then Exit Sub
Set objFontChoice = Selection.Font

Sheets(Array
("Coversheet", "MedRates", "MedBenefits", "DentalRates", _
"DentalBenefits", "STD", "LTD", "Life")).Select
Sheets("MedRates").Select
Range("a1:ax1").Select
With Selection.Font
.Name = objFontChoice.Name
End With
Range("A1").Select
Sheets("MedRates").Select
..

Macro
 

I believe you're using the wrong dialog. There are several
dialogs that format font. The dialog xlDialogFormatFont
seems to do what you want. Assuming you want to change the
font for ranges A1:AX1 for all sheets listed in the array
to the chosen font then perhaps this:

Sub ChangeFont()
Dim i As Integer
Dim ShtArr As Variant
Dim dlgAnswer As Boolean
Dim objFontChoice As Font
On Error Resume Next
ShtArr = Array
("Coversheet", "MedRates", "MedBenefits", "DentalRates", _
"DentalBenefits", "STD", "LTD", "Life")
Sheets("MedRates").Select
Range("a1").Select
dlgAnswer = Application.Dialogs(xlDialogFormatFont).Show
If dlgAnswer = False Then Exit Sub
Set objFontChoice = Selection.Font
For i = LBound(ShtArr) To UBound(ShtArr)
Sheets(ShtArr(i)).Range("A1:AX1").Font.Name =
objFontChoice.Name
Next
On Error GoTo 0
End Sub

Macro
 

Thank You very much.


-----Original Message-----
I believe you're using the wrong dialog. There are
several
dialogs that format font. The dialog xlDialogFormatFont
seems to do what you want. Assuming you want to change
the
font for ranges A1:AX1 for all sheets listed in the
array
to the chosen font then perhaps this:

Sub ChangeFont()
Dim i As Integer
Dim ShtArr As Variant
Dim dlgAnswer As Boolean
Dim objFontChoice As Font
On Error Resume Next
ShtArr = Array
("Coversheet", "MedRates", "MedBenefits", "DentalRates",
_
"DentalBenefits", "STD", "LTD", "Life")
Sheets("MedRates").Select
Range("a1").Select
dlgAnswer = Application.Dialogs(xlDialogFormatFont).Show
If dlgAnswer = False Then Exit Sub
Set objFontChoice = Selection.Font
For i = LBound(ShtArr) To UBound(ShtArr)
Sheets(ShtArr(i)).Range("A1:AX1").Font.Name =
objFontChoice.Name
Next
On Error GoTo 0
End Sub




.