peacenik
04-07-2008, 12:11 AM
I have a subroutine that modifies the ranges used by a series of charts, based on the data available. The subroutine "findlastwarow" determines the value of the variables "WLStart" and "lastWArow". Both these variables are dimensioned as Public integer within the module.
It looks to me as though the Chart 'WLChart' is not being updated when 'WLStart' changes. When I step through the code, the value WLStart is definitely being set correctly by findlastWArow but the chart is not being updated. Can anyone give me any ideas of what might be causing the problem.
Thanks in advance for any and all feedback.:banghead:
Sub updatebranchcharts()
findlastwarow
Set Dash = ThisWorkbook.Sheets("Branch Dashboard")
On Error Resume Next
Dash.Unprotect
'Application.ScreenUpdating = False
'Settlements Chart
Set SettChart = Dash.ChartObjects("Chart 6")
SettChart.SeriesCollection(1).Values = "='Branch Workarea'!R2C15:R" & lastWArow & "C15"
SettChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C14:R" & lastWArow & "C14"
SettChart.SeriesCollection(1).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
SettChart.SeriesCollection(2).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
SettChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With SettChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
SettChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With SettChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
SettChart.Axes(xlValue, xlSecondary).TickLabels.AutoScaleFont = False
With SettChart.Axes(xlValue, xlSecondary).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Applications Chart
Set AppChart = Dash.ChartObjects("Chart 7")
'Applications $ value actual
AppChart.SeriesCollection(1).Values = "='Branch Workarea'!R2C12:R" & lastWArow & "C12"
' Application $ value Targets
' AppChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C16:R" & lastWArow & "C16"
'No of Leads
AppChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C6:R" & lastWArow & "C6"
'No of Applications
AppChart.SeriesCollection(3).Values = "='Branch Workarea'!R2C11:R" & lastWArow & "C11"
'Months
AppChart.SeriesCollection(1).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
AppChart.SeriesCollection(2).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
AppChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With AppChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
AppChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With AppChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
AppChart.Axes(xlValue, xlSecondary).TickLabels.AutoScaleFont = False
With AppChart.Axes(xlValue, xlSecondary).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Conversion Rate Chart
Set ConvChart = Dash.ChartObjects("Chart 14")
'Converstion Rate data
ConvChart.SeriesCollection(1).Values = "='Branch Workarea'!R4C27:R" & lastWArow & "C27"
'Months
ConvChart.SeriesCollection(1).XValues = "='Branch Workarea'!R4C5:R" & lastWArow & "C5"
ConvChart.SeriesCollection(2).XValues = "='Branch Workarea'!R4C5:R" & lastWArow & "C5"
ConvChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With ConvChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
ConvChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With ConvChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Loan Book Chart
Set LBChart = Dash.ChartObjects("Chart 15")
'XXXX Loan Book
LBChart.SeriesCollection(1).Values = "='Branch Workarea'!R" & LBStart & "C18:R" & lastWArow - 2 & "C18"
'BC Loan Book
LBChart.SeriesCollection(2).Values = "='Branch Workarea'!R" & LBStart & "C25:R" & lastWArow - 2 & "C25"
'Months
LBChart.SeriesCollection(1).XValues = "='Branch Workarea'!R" & LBStart & "C5:R" & lastWArow - 2 & "C5"
LBChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With LBChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
LBChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With LBChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Settlements BC Mix Chart
Set BCMixChart = Dash.ChartObjects("Chart 12")
'XXXX Settlements
BCMixChart.SeriesCollection(1).Values = "='Branch Workarea'!R2C10:R" & lastWArow & "C10"
'BC Settlements
BCMixChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C26:R" & lastWArow & "C26"
'Months
BCMixChart.SeriesCollection(1).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
BCMixChart.SeriesCollection(2).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
BCMixChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With BCMixChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
BCMixChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With BCMixChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Product Mix Chart
Set ProdMixChart = Dash.ChartObjects("Chart 16")
'Product Data
ProdMixChart.SeriesCollection(1).Values = "='Branch Workarea'!R" & lastWArow - 2 & "C19:R" & lastWArow - 2 & "C25"
'XXXXlink Usage Chart
Set WLChart = Dash.ChartObjects("Chart 18")
'Mailshots
WLChart.SeriesCollection(1).Values = "='Branch Workarea'!R" & WLStart & "C30:R" & lastWArow & "C30"
'BAPS
WLChart.SeriesCollection(2).Values = "='Branch Workarea'!R" & WLStart & "C31:R" & lastWArow & "C31"
'Own Records
WLChart.SeriesCollection(3).Values = "='Branch Workarea'!R" & WLStart & "C32:R" & lastWArow & "C32"
'Activity
WLChart.SeriesCollection(4).Values = "='Branch Workarea'!R" & WLStart & "C33:R" & lastWArow & "C33"
'Months
WLChart.SeriesCollection(1).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.SeriesCollection(2).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.SeriesCollection(3).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.SeriesCollection(4).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With WLChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
WLChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With WLChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
WLChart.Axes(xlValue).Axes(xlValue, xlSecondary).TickLabels.AutoScaleFont = False
With WLChart.Axes(xlValue).Axes(xlValue, xlSecondary).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
Dash.Range("A1").Activate
Application.ScreenUpdating = True
Dash.Protect
End Sub
It looks to me as though the Chart 'WLChart' is not being updated when 'WLStart' changes. When I step through the code, the value WLStart is definitely being set correctly by findlastWArow but the chart is not being updated. Can anyone give me any ideas of what might be causing the problem.
Thanks in advance for any and all feedback.:banghead:
Sub updatebranchcharts()
findlastwarow
Set Dash = ThisWorkbook.Sheets("Branch Dashboard")
On Error Resume Next
Dash.Unprotect
'Application.ScreenUpdating = False
'Settlements Chart
Set SettChart = Dash.ChartObjects("Chart 6")
SettChart.SeriesCollection(1).Values = "='Branch Workarea'!R2C15:R" & lastWArow & "C15"
SettChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C14:R" & lastWArow & "C14"
SettChart.SeriesCollection(1).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
SettChart.SeriesCollection(2).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
SettChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With SettChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
SettChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With SettChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
SettChart.Axes(xlValue, xlSecondary).TickLabels.AutoScaleFont = False
With SettChart.Axes(xlValue, xlSecondary).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Applications Chart
Set AppChart = Dash.ChartObjects("Chart 7")
'Applications $ value actual
AppChart.SeriesCollection(1).Values = "='Branch Workarea'!R2C12:R" & lastWArow & "C12"
' Application $ value Targets
' AppChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C16:R" & lastWArow & "C16"
'No of Leads
AppChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C6:R" & lastWArow & "C6"
'No of Applications
AppChart.SeriesCollection(3).Values = "='Branch Workarea'!R2C11:R" & lastWArow & "C11"
'Months
AppChart.SeriesCollection(1).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
AppChart.SeriesCollection(2).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
AppChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With AppChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
AppChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With AppChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
AppChart.Axes(xlValue, xlSecondary).TickLabels.AutoScaleFont = False
With AppChart.Axes(xlValue, xlSecondary).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Conversion Rate Chart
Set ConvChart = Dash.ChartObjects("Chart 14")
'Converstion Rate data
ConvChart.SeriesCollection(1).Values = "='Branch Workarea'!R4C27:R" & lastWArow & "C27"
'Months
ConvChart.SeriesCollection(1).XValues = "='Branch Workarea'!R4C5:R" & lastWArow & "C5"
ConvChart.SeriesCollection(2).XValues = "='Branch Workarea'!R4C5:R" & lastWArow & "C5"
ConvChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With ConvChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
ConvChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With ConvChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Loan Book Chart
Set LBChart = Dash.ChartObjects("Chart 15")
'XXXX Loan Book
LBChart.SeriesCollection(1).Values = "='Branch Workarea'!R" & LBStart & "C18:R" & lastWArow - 2 & "C18"
'BC Loan Book
LBChart.SeriesCollection(2).Values = "='Branch Workarea'!R" & LBStart & "C25:R" & lastWArow - 2 & "C25"
'Months
LBChart.SeriesCollection(1).XValues = "='Branch Workarea'!R" & LBStart & "C5:R" & lastWArow - 2 & "C5"
LBChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With LBChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
LBChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With LBChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Settlements BC Mix Chart
Set BCMixChart = Dash.ChartObjects("Chart 12")
'XXXX Settlements
BCMixChart.SeriesCollection(1).Values = "='Branch Workarea'!R2C10:R" & lastWArow & "C10"
'BC Settlements
BCMixChart.SeriesCollection(2).Values = "='Branch Workarea'!R2C26:R" & lastWArow & "C26"
'Months
BCMixChart.SeriesCollection(1).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
BCMixChart.SeriesCollection(2).XValues = "='Branch Workarea'!R2C5:R" & lastWArow & "C5"
BCMixChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With BCMixChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
BCMixChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With BCMixChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
'Product Mix Chart
Set ProdMixChart = Dash.ChartObjects("Chart 16")
'Product Data
ProdMixChart.SeriesCollection(1).Values = "='Branch Workarea'!R" & lastWArow - 2 & "C19:R" & lastWArow - 2 & "C25"
'XXXXlink Usage Chart
Set WLChart = Dash.ChartObjects("Chart 18")
'Mailshots
WLChart.SeriesCollection(1).Values = "='Branch Workarea'!R" & WLStart & "C30:R" & lastWArow & "C30"
'BAPS
WLChart.SeriesCollection(2).Values = "='Branch Workarea'!R" & WLStart & "C31:R" & lastWArow & "C31"
'Own Records
WLChart.SeriesCollection(3).Values = "='Branch Workarea'!R" & WLStart & "C32:R" & lastWArow & "C32"
'Activity
WLChart.SeriesCollection(4).Values = "='Branch Workarea'!R" & WLStart & "C33:R" & lastWArow & "C33"
'Months
WLChart.SeriesCollection(1).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.SeriesCollection(2).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.SeriesCollection(3).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.SeriesCollection(4).XValues = "='Branch Workarea'!R" & WLStart & "C5:R" & lastWArow & "C5"
WLChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With WLChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
WLChart.Axes(xlValue).TickLabels.AutoScaleFont = False
With WLChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
WLChart.Axes(xlValue).Axes(xlValue, xlSecondary).TickLabels.AutoScaleFont = False
With WLChart.Axes(xlValue).Axes(xlValue, xlSecondary).TickLabels.Font
.Name = "Arial"
.Size = 9.5
End With
Dash.Range("A1").Activate
Application.ScreenUpdating = True
Dash.Protect
End Sub