jo15765
02-27-2012, 08:45 AM
I found this code here:
http://www.vbaexpress.com/forum/archive/index.php/t-27134.html
Option Explicit
Sub remove0Labels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
'dataLabelType = .DataLaboels.Type.xlValue
nPts = .Points.Count
aVals = .values
For iPts = 1 To nPts
Set po = .Points(iPts)
If aVals(iPts) = 0 Then
'po.DataLabel.NumberFormat
.Points(iPts).HasDataLabel = False
Else
End If
Next
End If
End With
Next
End If
End Sub
I modified the code slightly to this:
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point
Dim k As Long
For k = 1 To 2
With Sheets(k)
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
'dataLabelType = .DataLaboels.Type.xlValue
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
Set po = .Points(iPts)
If aVals(iPts) = 0 Then
'po.DataLabel.NumberFormat
.Points(iPts).HasDataLabel = False
Else
End If
Next
End If
End With
Next
End With
Next k
Which I thought would cycle through to sheets, but I am getting an object variable or with block variable not set debug error.
http://www.vbaexpress.com/forum/archive/index.php/t-27134.html
Option Explicit
Sub remove0Labels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, _
"No Chart Selected"
Else
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
'dataLabelType = .DataLaboels.Type.xlValue
nPts = .Points.Count
aVals = .values
For iPts = 1 To nPts
Set po = .Points(iPts)
If aVals(iPts) = 0 Then
'po.DataLabel.NumberFormat
.Points(iPts).HasDataLabel = False
Else
End If
Next
End If
End With
Next
End If
End Sub
I modified the code slightly to this:
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
Dim dataLabelType As Integer
Dim po As Point
Dim k As Long
For k = 1 To 2
With Sheets(k)
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
'dataLabelType = .DataLaboels.Type.xlValue
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
Set po = .Points(iPts)
If aVals(iPts) = 0 Then
'po.DataLabel.NumberFormat
.Points(iPts).HasDataLabel = False
Else
End If
Next
End If
End With
Next
End With
Next k
Which I thought would cycle through to sheets, but I am getting an object variable or with block variable not set debug error.