How to set chart series border property with variables read from cellvalues?

[JimC]

I'm trying to set the border properties of a chart series via
variables pulled from a "setup" worksheet but I'm getting an "Unable
to set the Weight property of the Border class" error.

Here's the relevant code snippets:

Option Explicit
' Property variables
Dim CxLine As Long
Dim WtLine As String
Dim StLine As String

' go and get the variables from the worksheet
' this part works and the variables get assigned the following values
' CxLine = 5
' WtLine = xlThin
' StLine = xlContinuous
' code continues to run

With .SeriesCollection.NewSeries
.Values = rngChtData
.XValues = rngChtXVal
.Name = Range(shtChartDef & "!" & rngSeriesName).Cells(RowNum)
.AxisGroup = xlPrimary
.Smooth = True
With .Border
.ColorIndex = CxLine
' ColorIndex is set correctly
.Weight = WtLine
' Error thrown here
' Locals window shows WtLine = xlThin
' changing line to 'Weight = xlThin or Weight = 2 works fine
.LineStyle = StLine
End With
.MarkerSize = 5
.Shadow = False
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
End With


I'm stumped - I'm missing something obvious here. Any ideas?

Jim.

 

[Jim Cone]

xlThin is a built-in Constant (variable) with a fixed value of 2.
So your "WtLine" variable should be declared as a Long not a String.

However you don't need the WtLine variable as you can use
the xlThin constant anyplace in your code where a value of 2 is needed.
--
Jim Cone
Portland, Oregon USA


"JimC" <[email protected]>
wrote in message
I'm trying to set the border properties of a chart series via
variables pulled from a "setup" worksheet but I'm getting an "Unable
to set the Weight property of the Border class" error.

Here's the relevant code snippets:

Option Explicit
' Property variables
Dim CxLine As Long
Dim WtLine As String
Dim StLine As String

' go and get the variables from the worksheet
' this part works and the variables get assigned the following values
' CxLine = 5
' WtLine = xlThin
' StLine = xlContinuous
' code continues to run

With .SeriesCollection.NewSeries
.Values = rngChtData
.XValues = rngChtXVal
.Name = Range(shtChartDef & "!" & rngSeriesName).Cells(RowNum)
.AxisGroup = xlPrimary
.Smooth = True
With .Border
.ColorIndex = CxLine
' ColorIndex is set correctly
.Weight = WtLine
' Error thrown here
' Locals window shows WtLine = xlThin
' changing line to 'Weight = xlThin or Weight = 2 works fine
.LineStyle = StLine
End With
.MarkerSize = 5
.Shadow = False
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
End With


I'm stumped - I'm missing something obvious here. Any ideas?

Jim.

 

[JimC]

xlThin is a built-in Constant (variable) with a fixed value of 2.
So your "WtLine" variable should be declared as a Long not a String.

The clue was there but I missed it - when trying variants of WtLine
assignments I noticed that with WtLine = xlThin it's value was shown
as 2 in the flyover evaluation. I didn't put it together.
Declaring it as Long didn't work as it was a type mismatch with the
cell contents "xlThin" (string).
Changing the declaration to Variant solved it.
Thanks for the solution.
Jim.