PDA

View Full Version : Apply only the Font part of a Style



dlh
03-10-2009, 05:07 PM
I'm trying to automate some fancy formatting and I'm looking for character-based style functionality in Excel (rather than cell-based).

Both of the following statements don't work ("subscript out of range" errors). Can anyone explain why?

Set MyCell.Characters(1, 5).Font = ThisWorkbook.Styles("MyStyle").Font

Set MyCell.Font = ThisWorkbook.Styles("MyStyle").Font

I know I can manually define each individual Font object member within a Characters object:

MyCell.Characters(1, 5).Font.Italic = True
MyCell.Characters(1, 5).Font.Name = "Courier"
MyCell.Characters(1, 5).Font.Size = 11
'etc. etc.
But is there anything more elegant and concise?

mdmackillop
03-10-2009, 05:42 PM
Sub SetFont()
Dim MyCell As Range, Fnt As String
Set MyCell = Range("A1")
Fnt = ActiveWorkbook.Styles("Mystyle").Font.Name
MyCell.Characters(1, 5).Font.Name = Fnt
End Sub

dlh
03-10-2009, 06:05 PM
Thank you, mdmackillop! I definitely can pull all the assignments into a function to be called multiple times. It's more concise, that's for sure, but not quite elegant.

Sub CopyFont(MyCharacters As Characters, MyStyle As Style)

MyCharacters.Font.Color = MyStyle.Font.Color
MyCharacters.Font.Bold = MyStyle.Font.Bold
MyCharacters.Font.Italic = MyStyle.Font.Italic
MyCharacters.Font.Name = MyStyle.Font.Name
MyCharacters.Font.Size = MyStyle.Font.Size
MyCharacters.Font.Strikethrough = MyStyle.Font.Strikethrough
MyCharacters.Font.Subscript = MyStyle.Font.Subscript
MyCharacters.Font.Superscript = MyStyle.Font.Superscript
MyCharacters.Font.Underline = MyStyle.Font.Underline

End Sub

Is there any reason why Font objects cannot be directly copied?

Bob Phillips
03-11-2009, 01:37 AM
You can make it more elegenat



Sub CopyFont(MyCharacters As Characters, MyStyle As Style)
Dim myFont As Font

myFont = MyStyle.Font
With MyCharacters.Font

.Color = myFont.Color
.Font.Bold = myFont.Bold
.Font.Italic = myFont.Italic
.Font.Name = myFont.Name
.Font.Size = myFont.Size
.Font.Strikethrough = myFont.Strikethrough
.Font.Subscript = myFont.Subscript
.Font.Superscript = myFont.Superscript
.Font.Underline = myFont.Underline
End With
End Sub


or even pass the font as the argument not the style