How To Modify One Point In A Graph...

Hi all,
I have a problem I cannot solve.. I think it is an EXCEL BUG !!
Could somebody have a quick lok at my spreadsheet.. it should take only
few minutes... otherwise, here is the details of my concern....

I have 2 series (Credit and Volatility) on the same graph.

Date Credit Volatility B/S Flag
25/03/04 30 42 B
25/04/04 35 45 S
25/05/04 34 43
25/06/04 38 42 S


I want to change the shape of specific points : put a big green square
when there is a "B" flag, or put a Big red square when there is a "S"
flag (see the spreadhsset enclosed)

Everything is fine for the second serie but the first one generate
completely random markerstyle and color instead of a red or green
square !
I am calling the same function for both (see in the VBA code) !!

PLEASE..... HELP !!!!!!
Is it a bug or am I becoming mad ???????


Here is the VBA code... long but easy to read...

Public Sub GenerateGraph()

'extract the number of elements for the graph
nbData = wsGraph.Range("GRAPH_NB_DATA")

'Generate the X, Y1 and Y2 strings to generate the graph
X_String = "=Graphics!R3C4:R" & nbData + 2 & "C4"
Y1_String = "=Graphics!R3C5:R" & nbData + 2 & "C5"
Y2_String = "=Graphics!R3C6:R" & nbData + 2 & "C6"

'Now, create the graph with the string generated above
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 10").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = X_String
ActiveChart.SeriesCollection(1).Values = Y1_String
ActiveChart.SeriesCollection(2).XValues = X_String
ActiveChart.SeriesCollection(2).Values = Y2_String

'At this point, set the first serie with a uniform and clean
'line (with a triangle for each point and color 32=Blue)
'(Macro below...)
SetGraphPoint 1, 32, xlTriangle

'At this point, set the first serie with a uniform and clean
'line (with a square for each point and color 7=magenta)
SetGraphPoint 2, 7, xlSquare

'And now the BUG !!!
'change the pattern if there is a B in the column G
'and put a green big square
'or put a red big square if there is a S
For i = 1 To nbData
If wsGraph.Cells(2 + i, 7) = "B" Then
'Do the "green conversion" for the first serie '
'(Macro below...)
SetGraphPoint_BUY 1, i
'Do the "green conversion" for the second serie
SetGraphPoint_BUY 2, i

ElseIf wsGraph.Cells(2 + i, 7) = "S" Then
'Do the "red conversion" for the first serie
'(Macro below...)
SetGraphPoint_SELL 1, i
'Do the "red conversion" for the second serie
SetGraphPoint_SELL 2, i
End If
Next i

'OK, it is finished !!
wsGraph.Range("A1").Select
Application.ScreenUpdating = True

End Sub

'Here we set the irGraph-th graphic to be with the irColor color and
with the irMarkerStyle Marker
' in order to have a clean and uniform graph
Private Sub SetGraphPoint(irGraph, irColor, irMarkerStyle)
ActiveChart.SeriesCollection(irGraph).Select
With Selection
.Border.ColorIndex = irColor
.Border.Weight = xlMedium
.Border.LineStyle = xlContinuous
.MarkerBackgroundColorIndex = irColor
.MarkerForegroundColorIndex = irColor
.MarkerStyle = irMarkerStyle
.Smooth = True
.MarkerSize = 6
.Shadow = False
End With
End Sub

'Here I modify the irPoint-ht point in the irGraph-th graph with a big
green square marker with a black cross
Private Sub SetGraphPoint_BUY(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoi nt).Select
With Selection
.MarkerBackgroundColorIndex = 4
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlX
.MarkerSize = 8
.Shadow = False
End With
End Sub


'Here I modify the irPoint-ht point in the irGraph-th graph with a big
red square marker with a black star
Private Sub SetGraphPoint_SELL(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoi nt).Select
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlStar
.MarkerSize = 8
.Shadow = False
End With
End Sub


+-------------------------------------------------------------------+
|Filename: TEST.zip |
|Download: http://www.excelforum.com/attachment.php?postid=2809 |
+-------------------------------------------------------------------+

--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=320351

Hi Pierre,

Your chart had me fooled for a moment. I though you had indeed found a
bug. But no, select the Credit data series and on the Options tab of the
Format dialog uncheck the 'Vary Colours by Points'.
Your code should work as expected.

Cheers
Andy

Pierre wrote:
Hi all,
I have a problem I cannot solve.. I think it is an EXCEL BUG !!
Could somebody have a quick lok at my spreadsheet.. it should take only
few minutes... otherwise, here is the details of my concern....

I have 2 series (Credit and Volatility) on the same graph.

Date Credit Volatility B/S Flag
25/03/04 30 42 B
25/04/04 35 45 S
25/05/04 34 43
25/06/04 38 42 S


I want to change the shape of specific points : put a big green square
when there is a "B" flag, or put a Big red square when there is a "S"
flag (see the spreadhsset enclosed)

Everything is fine for the second serie but the first one generate
completely random markerstyle and color instead of a red or green
square !
I am calling the same function for both (see in the VBA code) !!

PLEASE..... HELP !!!!!!
Is it a bug or am I becoming mad ???????


Here is the VBA code... long but easy to read...

Public Sub GenerateGraph()

'extract the number of elements for the graph
nbData = wsGraph.Range("GRAPH_NB_DATA")

'Generate the X, Y1 and Y2 strings to generate the graph
X_String = "=Graphics!R3C4:R" & nbData + 2 & "C4"
Y1_String = "=Graphics!R3C5:R" & nbData + 2 & "C5"
Y2_String = "=Graphics!R3C6:R" & nbData + 2 & "C6"

'Now, create the graph with the string generated above
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 10").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = X_String
ActiveChart.SeriesCollection(1).Values = Y1_String
ActiveChart.SeriesCollection(2).XValues = X_String
ActiveChart.SeriesCollection(2).Values = Y2_String

'At this point, set the first serie with a uniform and clean
'line (with a triangle for each point and color 32=Blue)
'(Macro below...)
SetGraphPoint 1, 32, xlTriangle

'At this point, set the first serie with a uniform and clean
'line (with a square for each point and color 7=magenta)
SetGraphPoint 2, 7, xlSquare

'And now the BUG !!!
'change the pattern if there is a B in the column G
'and put a green big square
'or put a red big square if there is a S
For i = 1 To nbData
If wsGraph.Cells(2 + i, 7) = "B" Then
'Do the "green conversion" for the first serie '
'(Macro below...)
SetGraphPoint_BUY 1, i
'Do the "green conversion" for the second serie
SetGraphPoint_BUY 2, i

ElseIf wsGraph.Cells(2 + i, 7) = "S" Then
'Do the "red conversion" for the first serie
'(Macro below...)
SetGraphPoint_SELL 1, i
'Do the "red conversion" for the second serie
SetGraphPoint_SELL 2, i
End If
Next i

'OK, it is finished !!
wsGraph.Range("A1").Select
Application.ScreenUpdating = True

End Sub

'Here we set the irGraph-th graphic to be with the irColor color and
with the irMarkerStyle Marker
' in order to have a clean and uniform graph
Private Sub SetGraphPoint(irGraph, irColor, irMarkerStyle)
ActiveChart.SeriesCollection(irGraph).Select
With Selection
Border.ColorIndex = irColor
Border.Weight = xlMedium
Border.LineStyle = xlContinuous
MarkerBackgroundColorIndex = irColor
MarkerForegroundColorIndex = irColor
MarkerStyle = irMarkerStyle
Smooth = True
MarkerSize = 6
Shadow = False
End With
End Sub

'Here I modify the irPoint-ht point in the irGraph-th graph with a big
green square marker with a black cross
Private Sub SetGraphPoint_BUY(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoi nt).Select
With Selection
MarkerBackgroundColorIndex = 4
MarkerForegroundColorIndex = 1
MarkerStyle = xlX
MarkerSize = 8
Shadow = False
End With
End Sub


'Here I modify the irPoint-ht point in the irGraph-th graph with a big
red square marker with a black star
Private Sub SetGraphPoint_SELL(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoi nt).Select
With Selection
MarkerBackgroundColorIndex = 3
MarkerForegroundColorIndex = 1
MarkerStyle = xlStar
MarkerSize = 8
Shadow = False
End With
End Sub


+-------------------------------------------------------------------+
|Filename: TEST.zip |
|Download: http://www.excelforum.com/attachment.php?postid=2809 |
+-------------------------------------------------------------------+


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Pierre,

Your chart had me fooled for a moment. I though you had indeed found a
bug. But no, select the Credit data series and on the Options tab of the
Format dialog uncheck the 'Vary Colours by Points'.
Your code should work as expected.

Cheers
Andy

Pierre wrote:
Hi all,
I have a problem I cannot solve.. I think it is an EXCEL BUG !!
Could somebody have a quick lok at my spreadsheet.. it should take only
few minutes... otherwise, here is the details of my concern....

I have 2 series (Credit and Volatility) on the same graph.

Date Credit Volatility B/S Flag
25/03/04 30 42 B
25/04/04 35 45 S
25/05/04 34 43
25/06/04 38 42 S


I want to change the shape of specific points : put a big green square
when there is a "B" flag, or put a Big red square when there is a "S"
flag (see the spreadhsset enclosed)

Everything is fine for the second serie but the first one generate
completely random markerstyle and color instead of a red or green
square !
I am calling the same function for both (see in the VBA code) !!

PLEASE..... HELP !!!!!!
Is it a bug or am I becoming mad ???????


Here is the VBA code... long but easy to read...

Public Sub GenerateGraph()

'extract the number of elements for the graph
nbData = wsGraph.Range("GRAPH_NB_DATA")

'Generate the X, Y1 and Y2 strings to generate the graph
X_String = "=Graphics!R3C4:R" & nbData + 2 & "C4"
Y1_String = "=Graphics!R3C5:R" & nbData + 2 & "C5"
Y2_String = "=Graphics!R3C6:R" & nbData + 2 & "C6"

'Now, create the graph with the string generated above
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 10").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = X_String
ActiveChart.SeriesCollection(1).Values = Y1_String
ActiveChart.SeriesCollection(2).XValues = X_String
ActiveChart.SeriesCollection(2).Values = Y2_String

'At this point, set the first serie with a uniform and clean
'line (with a triangle for each point and color 32=Blue)
'(Macro below...)
SetGraphPoint 1, 32, xlTriangle

'At this point, set the first serie with a uniform and clean
'line (with a square for each point and color 7=magenta)
SetGraphPoint 2, 7, xlSquare

'And now the BUG !!!
'change the pattern if there is a B in the column G
'and put a green big square
'or put a red big square if there is a S
For i = 1 To nbData
If wsGraph.Cells(2 + i, 7) = "B" Then
'Do the "green conversion" for the first serie '
'(Macro below...)
SetGraphPoint_BUY 1, i
'Do the "green conversion" for the second serie
SetGraphPoint_BUY 2, i

ElseIf wsGraph.Cells(2 + i, 7) = "S" Then
'Do the "red conversion" for the first serie
'(Macro below...)
SetGraphPoint_SELL 1, i
'Do the "red conversion" for the second serie
SetGraphPoint_SELL 2, i
End If
Next i

'OK, it is finished !!
wsGraph.Range("A1").Select
Application.ScreenUpdating = True

End Sub

'Here we set the irGraph-th graphic to be with the irColor color and
with the irMarkerStyle Marker
' in order to have a clean and uniform graph
Private Sub SetGraphPoint(irGraph, irColor, irMarkerStyle)
ActiveChart.SeriesCollection(irGraph).Select
With Selection
Border.ColorIndex = irColor
Border.Weight = xlMedium
Border.LineStyle = xlContinuous
MarkerBackgroundColorIndex = irColor
MarkerForegroundColorIndex = irColor
MarkerStyle = irMarkerStyle
Smooth = True
MarkerSize = 6
Shadow = False
End With
End Sub

'Here I modify the irPoint-ht point in the irGraph-th graph with a big
green square marker with a black cross
Private Sub SetGraphPoint_BUY(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoi nt).Select
With Selection
MarkerBackgroundColorIndex = 4
MarkerForegroundColorIndex = 1
MarkerStyle = xlX
MarkerSize = 8
Shadow = False
End With
End Sub


'Here I modify the irPoint-ht point in the irGraph-th graph with a big
red square marker with a black star
Private Sub SetGraphPoint_SELL(irGraph, IrPoint)
ActiveChart.SeriesCollection(irGraph).Points(IrPoi nt).Select
With Selection
MarkerBackgroundColorIndex = 3
MarkerForegroundColorIndex = 1
MarkerStyle = xlStar
MarkerSize = 8
Shadow = False
End With
End Sub


+-------------------------------------------------------------------+
|Filename: TEST.zip |
|Download: http://www.excelforum.com/attachment.php?postid=2809 |
+-------------------------------------------------------------------+


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Many thanks Andy, this stuff was getting me crazy !!!


--
Pierre
------------------------------------------------------------------------
Pierre's Profile: http://www.excelforum.com/member.php...fo&userid=3754
View this thread: http://www.excelforum.com/showthread...hreadid=320351