Consulting

Results 1 to 3 of 3

Thread: VBA Excel 2011 Data Labels

  1. #1

    solved: VBA Excel 2011 Data Labels

    The following code add Data Labels:
    [vba]Sub AddDataLabels1()
    Dim seSales As Series
    Dim pts As Points
    Dim pt As Point
    Dim rngLabels As Range
    Dim iPointIndex As Integer

    Set rngLabels = Range("AB6:BK6")

    Set seSales = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    seSales.HasDataLabels = True

    Set pts = seSales.Points
    For Each pt In pts
    iPointIndex = iPointIndex + 1
    pt.DataLabel.Text = rngLabels.Cells(iPointIndex).Text
    pt.DataLabel.Font.Bold = True
    pt.DataLabel.Position = xlLabelPositionAbove
    Next pt
    End Sub
    [/vba]
    What should I add to be able to toggle this vba on/off?
    Should it be like
    [vba]Sub AddDataLabels1()
    Application.ScreenUpdating = False
    Dim seSales As Series
    Dim pts As Points
    Dim pt As Point
    Dim rngLabels As Range
    Dim iPointIndex As Integer

    Set rngLabels = Range("AB6:BK6")

    Set seSales = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    seSales.HasDataLabels = True

    Set pts = seSales.Points
    For Each pt In pts
    iPointIndex = iPointIndex + 1
    pt.DataLabel.Text = rngLabels.Cells(iPointIndex).Text
    pt.DataLabel.Font.Bold = True
    pt.DataLabel.Position = xlLabelPositionAbove
    Next pt
    Application.ScreenUpdating = True
    End Sub
    [/vba]
    Last edited by cyrilbrd; 10-01-2012 at 01:28 AM.

  2. #2
    [vba]Sub ToggleDataLabels()

    Dim lngSeries As Long
    Dim xx As DataLabels
    With ActiveSheet.ChartObjects(1).Chart
    For lngSeries = 1 To .SeriesCollection.Count
    With .SeriesCollection(lngSeries)
    .HasDataLabels = Not .HasDataLabels
    If .HasDataLabels Then
    With .DataLabels
    .ShowCategoryName = False
    .ShowValue = True
    .ShowSeriesName = False
    End With
    End If
    End With
    Next
    End With

    End Sub
    [/vba]
    The code above does what I am looking for, but I need the specific range stated in the previous post.

    Thanks.

  3. #3
    Solved with:
    [VBA]Sub ToggleDataLabelsFirstAxis1()

    Dim lngSeries As Long
    Dim xx As DataLabels
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

    .HasDataLabels = Not .HasDataLabels
    If .HasDataLabels Then
    With .DataLabels
    .ShowCategoryName = False
    .ShowValue = True
    .ShowSeriesName = False
    End With
    End If
    End With


    End Sub

    [/VBA]
    Set one code per series each assigned to a check box. Works fine. range not needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •