MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Chart Variables


Posted by Chris on November 27, 2001 2:36 AM

Can anyone help me!??

I'm trying to create a chart using VBA. There are countless possible options and formats to this chart.

What I have done is set up a table that will tell Excel how to format each series that is added to a chart. It is a simple column table with information such as linestyle, color index, markerstyle etc...

The code works fine apart for setting the linestyle and markerstyle. I keep getting an error with this area of the code. I have set a string variable for each and do a vlookup against the table to get me the relevant linestyle/markerstyle (eg.xlContinuous). I know the Vlookup works fine.

However, excel will not change the chart accordingly it says that it is 'unable to set the Linestyle property of the Border Class'.

I have attached the code below

Does anyone know how I can get round this problem?

Many thanks

Chris


Sub CreateChart()

Dim LStyle As String
Dim MStyle As String
Dim CI As Integer
Dim MBC As Integer
Dim MFC As Integer
Dim i As Integer

ChartClearAll

'On Error Resume Next

For i = 1 To CR - 1
Sheets("Interactive Chart").Activate

With ActiveChart.SeriesCollection.NewSeries

.Name = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 6, False)

.Values = Sheets("WCI Data Sheet Run Rate").Range(CellReference(i), Range(CellReference(i)).Offset(0, Range("control").Value))

.XValues = Sheets("WCI Data Sheet Run Rate").Range("B3", Sheets("WCI Data Sheet Run Rate").Range("B3").Offset(0, Sheets("WCI Data Sheet Run Rate").Range("control").Value))

End With

ActiveChart.SeriesCollection(WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 6, False).Select

CI = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 10, False)

LStyle = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 8, False)

MBC = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 11, False)

MFC = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 12, False)

MStyle = WorksheetFunction.VLookup(CellReference(i), Sheets("Master Table").Range("b2:t4033"), 9, False)

With Selection.Border
.ColorIndex = CI
.Weight = xlThin
.LineStyle = LStyle
End With

With Selection
.MarkerBackgroundColorIndex = MBC
.MarkerForegroundColorIndex = MFC
.MarkerStyle = MStyle
End With
Next i

End Sub


Posted by Damon Ostrander on December 03, 2001 8:14 AM

Hi Chris,

I suspect that in your table you identified the linestyle as strings such
as "xlContinuous", "xlDash", etc. If this is the case, then this is the
problem. These names are VBA named constants, not strings. For example,
the constant xlContinuous is really an integer value 1, while xlDash is
an integer value -4115, xlDashDot is 4, and xlDot is -4118. It is these
numbers that you must put into your table to achieve the desired results.
If you really want to use these descriptive names in your table, then you
must do some sort of lookup, such as:

Select Case LStyle
Case "xlContinuous": .MarkerStyle = xlContinuous
Case "xlDash": .MarkerStyle = xlDash
Case "xlDot": .MarkerStyle = xlDot
Case "xlDashDot": .MarkerStyle = xlDashDot
.
.
.
End Select


Happy computing.

Damon