How to change series color of dynamic Graph obj after changing rowsource at runtime

[TheDom]

Hi

This is my first post, so pls bear with me in formulation.

I have now tried for days to get this issue to work - without success. So I hope, somebody can help me.

I have a form with an MS graph object, some selectors and a command button. Clicking the command button generates the graph. The selectors enables the user to add data series - thus, I have a dynamic graph.

My code first sets the rowsource of the graph object and afterwards changes the color of the different data series according to predefined color codes regarding the data type. Thus, this happens at runtime.

My problem is the following: Updating the graph the first time (via cmd button) does not change the colors of the data series. I have to push the cmd button a second time, before the colors are changed according to the color codes.

Here is a sample of my code:
________________________

Private Sub cmdRefreshGraph_Click()
Dim strQueryName As String
Dim col() As Variant
Dim Counter As Integer

SetGlobalConst ' sub to set predefined color codes using global variables

Counter = 0
'If Data Type 1 is chosen, remember the color code 'colType1' (glob.var.)

If optType1.Value Then
Counter = Counter + 1
ReDim Preserve col(Counter)
col(Counter) = colType1
End If
If optType2.Value Then
Counter = Counter + 1
ReDim Preserve col(Counter)
col(Counter) = colType2
End If
If optType3.Value Then
Counter = Counter + 1
ReDim Preserve col(Counter)
col(Counter) = colType3
End If

' Build the query taking only selected data types
strQueryName = "SELECT Period, "
If optType1.Value Then strQueryName = strQueryName & "Sum([Data_1]) AS Data1, "
If optType2.Value Then strQueryName = strQueryName & "Sum([Data_1]) AS Data2, "
If optType3.Value Then strQueryName = strQueryName & "Sum([Data_1]) AS Data3, "
strQueryName = Left(strQueryName, Len(strQueryName) - 2)
strQueryName = strQueryName & " FROM DataTable " & _
"GROUP BY Period;"

' Set the RowSource
Me.page01gph01.RowSource = strQueryName

' Set the colors of different data series
For Counter = 1 To Me.page01gph01.SeriesCollection.Count
Me.page01gph01.SeriesCollection(Counter).Border.Color = col(Counter)
Me.page01gph01.SeriesCollection(Counter).Interior.Color = col(Counter)
Next Counter
End Sub

________________________

I really hope that somebody can help me.

 

[TheDom]

Is there really nobody, who has a clue?

I know, this is not easy. I have looked around fora for weeks now and couldn't get an answer. So I was hoping to find one hear. Any help is appreciated - also work-arounds.

Thx!

 

[ChrisO]

Try this:-

Private Sub page01gph01_Updated(Code As Integer)

    [color=green]' First set focus to the chart and then to another control.[/color]
    Me.page01gph01.SetFocus
    Me.cmdRefreshGraph.SetFocus

End Sub

Make sure page01gph01_Updated is called.

Hope that helps.

Chris.

 

[TheDom]

Hi Chris,

Thx for the reply. Unfortunately, that does not help. The graph stays with the old color selection and I still have to click a second time to get the new colors.

There must be something with the graph object, that it does not allow color manipulations after the rowsource has been set. I just cannot explain to myself why..

Any help is still appreciated!

Thx
Dom

 

[ChrisO]

Did you verify the code I supplied is being called?
Place a message box in it to verify.

Chris.

 

[TheDom]

Hi again Chris,

Yes, the code is called - a msgbox verifies it. However, it does not help. The focus is set accordingly, but the graph is not updated to the actual color codes.´

Dom