Chart Formatting changes with filter conditions

[math123987]

Hi all,
I have an excel workbook with 1 pivot table and 1 chart. In the chart I have set 3 fields as columns chart and one as line type. But whenever I change the filters from no data(when filter values mismatch) to data (matching filter values), the formatting goes and I can see only columns for all fields.
How can I keep the formatting(chart types) even when filter conditions change?
I have excel 2010. I am not finding worksheet_change event in the workbook and I tried typing it but the event is not running. Also I used Workbook_SheetPivotTableAfterValueChange, this is running multiple times.
I used the following codes.
Private Sub Workbook_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)
Call modulemacro1
If Sheet1.Range("E31,E34").Value <> 0 Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ChartType = xlLine
Else
MsgBox ("No data found")
End If

End Sub
Private Sub worksheet_change(ByVal Target As Range)
Call modulemacro1
If Sheet1.Range("E31,E34").Value <> 0 Then
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ChartType = xlLine
Else
MsgBox ("No data found")
End If
End Sub
Can anybody help me so that my chart formatting does not change even after changing the filter conditions.....

 

[gehusi]

Sounds like some dynamic charting is in order. Post your workbook and let's see what we can do.