More Chart pain
From
noodnutt@gmail.com@21:1/5 to
All on Thu Jan 30 18:40:14 2020
Hi All
I'm glad I came across this issue prior to releasing this file:
I would like to step over the Chart.add part of the code if there is no data for the selected filter.
I tried the following, and in-as-much as the MsgBox appears when there is no data, it does not GoTo stepOver and proceeds to create the shape, ideally I don't won't this.
TIA
Mark.
With shChar
With myPDsht
.PivotTables("Centre_Combo").PivotFields("CC").ClearAllFilters
If Not .PivotTables("Centre_Combo").PivotFields("CC").CurrentPage = "T-11" > 0 Then
myMess = MsgBox("There is no data available for this Cost Centre", vbOKOnly)
GoTo StepOver
Else
.PivotTables("Centre_Combo").PivotFields("CC").CurrentPage = "T-11"
End If
End With
With myChart.Chart
.SetSourceData Source:=myPDsht.Range("$A$6", myPDsht.Range("$C" & myPDsht.rows.count).End(xlUp))
.FullSeriesCollection(1).ChartType = xlLine
.FullSeriesCollection(1).AxisGroup = 1
.FullSeriesCollection(2).ChartType = xlLine
.FullSeriesCollection(2).AxisGroup = 1
.FullSeriesCollection(2).AxisGroup = 2
.ShowAllFieldButtons = False
.Parent.Top = 75
.Parent.Left = 200
.Parent.Height = 250
.Parent.Width = 750
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = myPDsht.Range("$B$4")
.Axes(xlValue, xlPrimary).Select
.Axes(xlValue, xlPrimary).MinimumScale = 0
.Axes(xlValue, xlPrimary).MaximumScale = 5000
.Axes(xlValue, xlPrimary).MajorUnit = 1000
.Axes(xlValue, xlPrimary).MinorUnit = 500
.Axes(xlValue, xlSecondary).Select
.Axes(xlValue, xlSecondary).MinimumScale = 0
.Axes(xlValue, xlSecondary).MaximumScale = 25000
.Axes(xlValue, xlSecondary).MajorUnit = 2500
.Axes(xlValue, xlSecondary).MinorUnit = 100
.Axes(xlCategory).CategoryType = xlCategoryScale
End With
End With
StepOver: Exit Sub
Application.Wait (Now + myTI * 250)
With shChar.ChartObjects(5)
.Activate
.Name = myChar
.Left = Range("B74").Left
.Top = Range("B74").Top
End With
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)