I have eight regions' sales data displayed in eight line charts on a form. The Visible property is changed to True or False dependent on which region is selected in a combo box, so all charts must be formatted and ready to go when a new chart is selected.
Additionally, the user can select or de-select series to display on the charts based on a subform linked to a table with an On/Off field "Display". The user selects or deselects series and then clicks a command button called "Recalculate", and the average for each region is appended into the region's table and the charts are requeried to show the new set of series.
The charts are formatted so that the regular series of data are default formatting and the Average series has a wider line thickness, larger marker (always a triangle), and is always displayed in black. The formatting for these charts takes about 30-45 seconds, and is done with the following code:
The only alternative to formatting all series that I could think of would be to somehow format the whole chart and then go back and only format the Average series, but the couple/few syntax possibilities I've attempted haven't worked.
Does anyone have any suggestions? I hate having a 45 second lag between the user clicking the button and the results being displayed. Thanks!
Additionally, the user can select or de-select series to display on the charts based on a subform linked to a table with an On/Off field "Display". The user selects or deselects series and then clicks a command button called "Recalculate", and the average for each region is appended into the region's table and the charts are requeried to show the new set of series.
The charts are formatted so that the regular series of data are default formatting and the Average series has a wider line thickness, larger marker (always a triangle), and is always displayed in black. The formatting for these charts takes about 30-45 seconds, and is done with the following code:
Code:
'Format the charts
For i = 1 To seriescount
Select Case i
Case seriescount
'Format Chart1
With Chart1.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart1.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
'Format Chart2
With Chart2.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart2.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
'Format Chart3
With Chart3.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart3.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
'Format Chart4
With Chart4.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart4.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
'Format Chart5
With Chart5.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart5.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
'Format Chart6
With Chart6.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart6.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
'Format Chart7
With Chart7.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart7.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
'Format Chart8
With Chart8.SeriesCollection(i).Border
.ColorIndex = 1
.Weight = -4138 '-4138 is Medium weight
End With
With Chart8.SeriesCollection(i)
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = 3 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
Case Else
'Format Chart1
With Chart1.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart1.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
'Format Chart2
With Chart2.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart2.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
'Format Chart3
With Chart3.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart3.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
'Format Chart4
With Chart4.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart4.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
'Format Chart5
With Chart5.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart5.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
'Format Chart6
With Chart6.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart6.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
'Format Chart7
With Chart7.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart7.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
'Format Chart8
With Chart8.SeriesCollection(i).Border
.ColorIndex = -4105
.Weight = 2 '2 is standardweight
End With
With Chart8.SeriesCollection(i)
.MarkerBackgroundColorIndex = -4105
.MarkerForegroundColorIndex = -4105
.MarkerStyle = -4105 'MarkerStyle 1 = Square, 2 = Diamond, 3 = Triangle, 6 = Dash, 8 = Circle
.Smooth = False
.MarkerSize = 4
.Shadow = False
End With
End Select
Next iThe only alternative to formatting all series that I could think of would be to somehow format the whole chart and then go back and only format the Average series, but the couple/few syntax possibilities I've attempted haven't worked.
Does anyone have any suggestions? I hate having a 45 second lag between the user clicking the button and the results being displayed. Thanks!