Excel2007 crashes while setting chart formatting

H

Hakyab

All I am trying is to set the color of the graph line:

With Charts("Ranks")
For i = 1 To 18
CurT = GrData.Cells(i, 1)
.SeriesCollection(i).Border.ColorIndex = TakimR.Cells(CurT, 5)
Next i
End With

Excel crashes when the fourth line above executes (when file is recovered
the line color for the first series is set correctly, so I guess it crashes
after executing). Same thing happens when I try to set Border.Weight as
well. On the other hand, Border.Color can be set without any crashes.

I tried including .clearformats before the problem line, but did not help.

Can anyone help me with this problem? I can at least do the color part if
some one could tell me how to convert ColorIndex to a color number.

Thanks,
Hakan
 
J

Jon Peltier

I crashed Excel also. Then I rearranged your code slightly, and used an
intermediate variable for the color index retrieved from the sheet. This
code didn't crash, and colored the lines according to the default Excel 2003
palette:

Sub ColorLines()
Dim CurT As Long
Dim GrData As Worksheet
Dim TakimR As Worksheet
Dim i As Long
Dim ColIdx As Long

Set GrData = Worksheets(1)
Set TakimR = Worksheets(1)

With Charts("Ranks")
For i = 1 To 18
CurT = GrData.Cells(i, 1).Value
ColIdx = TakimR.Cells(CurT, 5).Value
.SeriesCollection(i).Border.ColorIndex = ColIdx
Next i
End With

End Sub


- Jon
 
H

Hakyab

Thanks again Jon. Weird thing is, it worked when I appended .value at the
end, i.e.

.SeriesCollection(i).Border.ColorIndex = TakimR.Cells(CurT, 5).value

without intermediate variable. Hope someone from Microsoft is reading this.

Best,

Hakan
 
J

Jon Peltier

You should always use .Value at the end. It's bad practice to rely on the
default properties. Not that they're likely to change them, but weird stuff
happens whenever there's an upgrade.

You should also declare all of your variables, and reference objects fully,
like ActiveWorkbook.Worksheets("Sheet1") instead of just
Worksheets("Sheet1").

- Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top