Hi
I have found a macro that should work with a change of a specific cell.
Code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub
However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value).
The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:
Code
Sub Button1()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
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
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
End If
End Sub
Sub Button2()
Sheets("report_pl").Select
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
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
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
End If
End Sub
Sub Button3()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 137").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
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
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
End If
End Sub
Sub Button4()
Sheets("report_pl").Select
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
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
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
End If
End Sub
Display More