PDA

View Full Version : VBA Excel 2011 Data Labels



cyrilbrd
09-30-2012, 10:15 PM
The following code add Data Labels:
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

What should I add to be able to toggle this vba on/off?
Should it be like
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

cyrilbrd
10-01-2012, 12:58 AM
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

The code above does what I am looking for, but I need the specific range stated in the previous post.

Thanks.

cyrilbrd
10-01-2012, 01:27 AM
Solved with:
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


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