CODEWITHSUNDEEP
excelvbaexcel-2016
eirikdaude
eirikdaude

Reputation: 3256

Setting the font size in a shape

I have the following macro which is supposed to create a box linking to a certain worksheet in the workbook, on each sheet of the workbook:

Option Explicit

Sub gndhnkl()
    Dim ws As Worksheet
    Dim sh As Shape

    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.Name, "Summering", vbBinaryCompare) <= 0 Then
            For Each sh In ws.Shapes
                sh.Delete
            Next sh
            Call Macro1(ws)
        End If
    Next ws
End Sub

Sub Macro1(ws As Worksheet)

    Dim venstre As Double, topp As Double, breidde As Double, høgde As Double
    Dim sh As Shape

    venstre = ws.Range("B16").Left
    topp = ws.Range("B16").Top
    breidde = 110
    høgde = 68

    Set sh = ws.Shapes.AddShape(msoShapeRoundedRectangle, venstre, topp, breidde, høgde)
    With sh.TextFrame2.TextRange
        .Characters.Text = "Til summering, person"
        .Font.Size = 13
        .ParagraphFormat.Alignment = msoAlignCenter
        .Parent.VerticalAnchor = msoAnchorMiddle
    End With
    ws.Hyperlinks.Add Anchor:=sh, Address:="", SubAddress:=Replace(Summering_person.Range("A1").Address(external:=True), "[" & ThisWorkbook.Name & "]", "", 1, -1, vbBinaryCompare)

End Sub

For the most part it works just like I expect it too, but for some reason the font size in the added shape is not set to 13 as I expect, but remains 11.

I.e. it seems that the line .Font.Size = 13 (sh.TextFrame2.TextRange.Font.Size = 13) is not executed.

Where is my mistake here, and what do I need to do in order for the macro to set the font size for the shape?

Upvotes: 0

Views: 7891

Answers (1)

FunThomas
FunThomas

Reputation: 29146

You have to change the order, first set the font size (and any other font properties) before you write the text. Once the text is set, it's getting trickier to change the font - every character of the TextFrame may have it's own characteristics.

  .Font.Size = 13
  .Characters.Text = "Til summering, person"

Update The comment of SJR is right, when using the TextFrame rather than TextFrame2, you can set the font properties of the whole text as once after the text was written.

Upvotes: 3

Related Questions