Problem setting font size

[Edward Ulle]

How do you set the font size for the following?

Set sShape = worksheet.AddShape(msoShapeRectangle, left, top, width,
height)
With sShape
.Fill.Transparency = 1#
.Line.Transparency = 1#
With .TextFrame
.Characters.Text = label
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With
End With

Adding .Characters.Font.Size = 6 causes an error and using .AddLabel I
can set font size but can not center align the text.

 

[Markus Scheible]

Good Morning Edward,

Set sShape = worksheet.AddShape(msoShapeRectangle, left, top, width,
height)
With sShape
.Fill.Transparency = 1#
.Line.Transparency = 1#
With .TextFrame
.Characters.Text = label
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With
End With

Adding .Characters.Font.Size = 6 causes an error and using .AddLabel I
can set font size but can not center align the text.

I think the error is caused by your with command...
because .TextFrame.Characters.Font.Size won't be the right
address.

Try

Shapes.Characters.Font.Size = 25

Best

Markus

 

[Edward Ulle]

Markus

Through much trial and error. Lots of error. I finally got it to work
by including the following cText object.

Dim sShape As Shape
Dim cText As Characters ' Added

Set sShape = worksheet.AddShape(msoShapeRectangle, left,
top, width, height)
With sShape
.Fill.Transparency = 1#
.Line.Transparency = 1#
With .TextFrame
.Characters.Text = label
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With
End With

Set cText = sShape.TextFrame.Characters ' Added
cText.Font.Size = 6 ' Added

Thanks for your suggestion.

Ed