-
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.
-
[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.
-
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
-
Forum Rules