the code to change font names, size for textbox in Excel macro

Question

try to find the code to change font names, size for textbox in Excel macro, all my codes from " .font.name= to End with" shows wrong in VBA, any suggestions? thanks

Set myDocument = Worksheets(1)

  Set tx_ = myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 382, 266, 122, 20)

tx_.TextFrame.Characters.Text = ThisWorkbook.Sheets(1).Cells(6, 8)

  With tx_.TextFrame.Characters.Text

  .Font.Name = "Tahoma"

  .Font.Size = 10

  .Font.Bold = msoTrue

  End With

Try using .TextFrame.TextRange. instead of just .TextFrame.. It would be helpfult ot know what error message you are getting — cybernetic.nomad

JvdV JvdV · Accepted Answer · 2019-09-18T14:37:14

Whereas .Text is a valid property of the .Characters object, .Font is not a sub-property of it of some sort. It is a property to the object on it's own. Therefor the following worked for me:

Sub Tst()

Dim myDocument As Worksheet: Set myDocument = ThisWorkbook.Sheets("Sheet1")
Dim tx As Shape

Set tx = myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 382, 266, 122, 20)
With tx.TextFrame.Characters
    .Text = mydocument.Cells(6, 8)
    .Font.Name = "Tahoma"
    .Font.Size = 10
    .Font.Bold = msoTrue
End With

End Sub

the code to change font names, size for textbox in Excel macro

1 Answers