Tag: chart data label

Optimizing VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Add ‘OptimizeCode_Begin’ to the first line inside sub, and add ‘OptimizeCode_End’ to the last line inside sub.

Sub OptimizeCode_Begin()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

End Sub

Sub OptimizeCode_End()
    
    ActiveSheet.DisplayPageBreaks = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

Example of usage

Sub GroupBy()

    OptimizeCode_Begin

    'Enter your code between optimize code begin/end

    OptimizeCode_End

End Sub

Turn on/off chart data label using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Turn on/off chart data label

Sub DataLabel()

    Dim cbValue As Object
    Set cbValue = ActiveSheet.CheckBoxes("Check Box 1")
    
    Dim chtObj As ChartObject
    Dim sr As Series
    
    With cbValue
        If .Value = 1 Then
            For Each chtObj In ActiveSheet.ChartObjects
                For Each sr In chtObj.Chart.SeriesCollection
                    sr.ApplyDataLabels
                        With sr.DataLabels
          '             .ShowSeriesName = True
                        .ShowValue = True
           '            .Position = xlLabelPositionInsideBase
          '             .Orientation = -90
          '             .Font.Size = 8
                        End With
                Next sr
            Next chtObj
            
        Else
            For Each chtObj In ActiveSheet.ChartObjects
                For Each sr In chtObj.Chart.SeriesCollection
                    sr.ApplyDataLabels
                        With sr.DataLabels
          '             .ShowSeriesName = True
                        .ShowValue = False
           '            .Position = xlLabelPositionInsideBase
          '             .Orientation = -90
          '             .Font.Size = 8
                        End With
                Next sr
            Next chtObj
        
        End If
    End With
    
End Sub