none
Setting Data Label Font Format in Treemap via VBA RRS feed

  • Question

  • I am trying to set the font format of a datalabel in a treemap in a VBA macro, but i am getting errors:

    how do i access the datalabel and update the font - in the UI, i can right-click any data label and update the font settings.

    example; at the bold line i get a 438 error:

    pnts = ActiveChart.FullSeriesCollection(1).Points.Count
    Dim i As Integer
    Dim labelname As String
    Dim color As Long
    i = 0

    ActiveChart.FullSeriesCollection(1).Select



    For i = 1 To pnts
       ActiveSheet.Shapes.Range(Array("Chart 1")).Select
       ActiveChart.FullSeriesCollection(1).Select
        labelname = ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Text
        
        ActiveSheet.Cells(3, 5).Select
        
        ActiveSheet.Cells(i + 4, 9).Select
        color = Selection.DisplayFormat.Interior.color

         ActiveSheet.Shapes.Range(Array("Chart 1")).Select
       ActiveChart.FullSeriesCollection(1).Select
       ActiveChart.FullSeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = color
       
     ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Format.Font.Size = 12
      
    Next i

    Tuesday, January 15, 2019 6:30 PM

Answers

  • i found the correct instruction:

      ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Format.TextFrame2.TextRange.Font.Size = 12

    related:

    https://social.technet.microsoft.com/Forums/en-US/09a3b350-c77f-4a3e-a821-69456639d364/excel-change-data-label-font-size?forum=winserverpowershell

    • Marked as answer by kimmelj Thursday, January 17, 2019 2:01 PM
    Thursday, January 17, 2019 2:01 PM

All replies

  • Hi,

    Welcome to the Microsoft Office for IT Professionals Excel forum. This forum is for general questions and feedback related to Microsoft Excel. For your question, it’s mainly related to VBA. To help you better solve it, I will move the thread to Excel for Developer forum. Thanks for your kind understanding.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, January 16, 2019 2:12 AM
  • i found the correct instruction:

      ActiveChart.FullSeriesCollection(1).Points(i).DataLabel.Format.TextFrame2.TextRange.Font.Size = 12

    related:

    https://social.technet.microsoft.com/Forums/en-US/09a3b350-c77f-4a3e-a821-69456639d364/excel-change-data-label-font-size?forum=winserverpowershell

    • Marked as answer by kimmelj Thursday, January 17, 2019 2:01 PM
    Thursday, January 17, 2019 2:01 PM