Thread: Macro
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Greg Wilson[_4_] Greg Wilson[_4_] is offline
external usenet poster
 
Posts: 218
Default 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