Hi.

From Access I open Excel, export data to a sheet and then create a chart. It
works well, but now I want to make the chart graphics lines thinner.
The code I use to create the chart and graphics lines is shown below.
As you can see I (try to) use
..Chart.SeriesCollection(intField).LineFormat.Weig ht = 1
and
..Chart.SeriesCollection(intField).Shape.Line.Weig ht = 1
to set the width of the line(s), but both give me a 'property or method not
supported' (error 438) error.
I've tried lots of things but I think one of these lines of code must be
close to the answer.

Can anybody help me, please?
Thanks!

Ronald.

This is my code:
.Shapes.AddChart.Select
Set exlChart = .ChartObjects(1)
With [exlChart]
.Chart.SeriesCollection(1).Delete
.Chart.ChartType = xlLineMarkers
If (UBound(varTabel, 2) = 0) Then
'1 data rij
.Chart.Axes(xlCategory).AxisBetweenCategories = True
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65 +
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" & Chr(65
+ intField) & "$4"
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4"
' .Chart.SeriesCollection(intField).LineFormat.Weigh t = 1
Next intField
Else
'meerdere data rijen
.Chart.Axes(xlCategory).AxisBetweenCategories = False
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65 +
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" & Chr(65
+ intField) & "$4:" & Chr(65 + intField) & CStr(UBound(varTabel, 2) + 4)
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4:A" &
CStr(UBound(varTabel, 2) + 4)
' .Chart.SeriesCollection(intField).Shape.Line.Weigh t = 1
Next intField
End If

The macro recorder indicates that you need this syntax:

.Chart.SeriesCollection(intField).Border.Weight = xlHairline

Excel 2007 may have some enhanced formatting capabilities, but I have not
yet had the opportunity (or need) to figure it out. The documentation is
abysmal, even by Microsoft's low standards.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Ronald" <wit@yahoo wrote in message
...
Hi.

From Access I open Excel, export data to a sheet and then create a chart.
It
works well, but now I want to make the chart graphics lines thinner.
The code I use to create the chart and graphics lines is shown below.
As you can see I (try to) use
.Chart.SeriesCollection(intField).LineFormat.Weigh t = 1
and
.Chart.SeriesCollection(intField).Shape.Line.Weigh t = 1
to set the width of the line(s), but both give me a 'property or method
not
supported' (error 438) error.
I've tried lots of things but I think one of these lines of code must be
close to the answer.

Can anybody help me, please?
Thanks!

Ronald.

This is my code:
.Shapes.AddChart.Select
Set exlChart = .ChartObjects(1)
With [exlChart]
.Chart.SeriesCollection(1).Delete
.Chart.ChartType = xlLineMarkers
If (UBound(varTabel, 2) = 0) Then
'1 data rij
.Chart.Axes(xlCategory).AxisBetweenCategories = True
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65
+
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" &
Chr(65
+ intField) & "$4"
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4"
' .Chart.SeriesCollection(intField).LineFormat.Weigh t = 1
Next intField
Else
'meerdere data rijen
.Chart.Axes(xlCategory).AxisBetweenCategories = False
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65
+
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" &
Chr(65
+ intField) & "$4:" & Chr(65 + intField) & CStr(UBound(varTabel, 2) + 4)
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4:A" &
CStr(UBound(varTabel, 2) + 4)
' .Chart.SeriesCollection(intField).Shape.Line.Weigh t = 1
Next intField
End If

I solved this problem by making my own chart and then recording a macro while
changing the line width manually. Below is the code I got. Delete the lines
of code you don't need. The macro recorded always produces and lot of things
you don't really need. The weight of the line doesn't have numbers.

There is an Object Browser in VBA you get the setting of the line by going
to menu View - Object browswer. I entered xLThick and found the graph line
has four settings

1) xlHairLine = 1
2) xlMedium = -4138
3) xlthick = 4
4) xl Thin = 2

DON"T ASK WHY MICROSOFT CHOOSE -4138 for
medium!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Your change would be

from:
..Chart.SeriesCollection(intField).Shape.Line.Weig ht = 1

to:
..Chart.SeriesCollection(intField).Border.Weight = xlThick


ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With


"Ronald" wrote:

Hi.

From Access I open Excel, export data to a sheet and then create a chart. It
works well, but now I want to make the chart graphics lines thinner.
The code I use to create the chart and graphics lines is shown below.
As you can see I (try to) use
.Chart.SeriesCollection(intField).LineFormat.Weigh t = 1
and
.Chart.SeriesCollection(intField).Shape.Line.Weigh t = 1
to set the width of the line(s), but both give me a 'property or method not
supported' (error 438) error.
I've tried lots of things but I think one of these lines of code must be
close to the answer.

Can anybody help me, please?
Thanks!

Ronald.

This is my code:
.Shapes.AddChart.Select
Set exlChart = .ChartObjects(1)
With [exlChart]
.Chart.SeriesCollection(1).Delete
.Chart.ChartType = xlLineMarkers
If (UBound(varTabel, 2) = 0) Then
'1 data rij
.Chart.Axes(xlCategory).AxisBetweenCategories = True
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65 +
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" & Chr(65
+ intField) & "$4"
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4"
' .Chart.SeriesCollection(intField).LineFormat.Weigh t = 1
Next intField
Else
'meerdere data rijen
.Chart.Axes(xlCategory).AxisBetweenCategories = False
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65 +
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" & Chr(65
+ intField) & "$4:" & Chr(65 + intField) & CStr(UBound(varTabel, 2) + 4)
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4:A" &
CStr(UBound(varTabel, 2) + 4)
' .Chart.SeriesCollection(intField).Shape.Line.Weigh t = 1
Next intField
End If

Thank you both very much!

"Ronald" wrote:

Hi.

From Access I open Excel, export data to a sheet and then create a chart. It
works well, but now I want to make the chart graphics lines thinner.
The code I use to create the chart and graphics lines is shown below.
As you can see I (try to) use
.Chart.SeriesCollection(intField).LineFormat.Weigh t = 1
and
.Chart.SeriesCollection(intField).Shape.Line.Weigh t = 1
to set the width of the line(s), but both give me a 'property or method not
supported' (error 438) error.
I've tried lots of things but I think one of these lines of code must be
close to the answer.

Can anybody help me, please?
Thanks!

Ronald.

This is my code:
.Shapes.AddChart.Select
Set exlChart = .ChartObjects(1)
With [exlChart]
.Chart.SeriesCollection(1).Delete
.Chart.ChartType = xlLineMarkers
If (UBound(varTabel, 2) = 0) Then
'1 data rij
.Chart.Axes(xlCategory).AxisBetweenCategories = True
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65 +
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" & Chr(65
+ intField) & "$4"
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4"
' .Chart.SeriesCollection(intField).LineFormat.Weigh t = 1
Next intField
Else
'meerdere data rijen
.Chart.Axes(xlCategory).AxisBetweenCategories = False
For intField = 1 To UBound(varTabel, 1)
.Chart.SeriesCollection.NewSeries
.Chart.SeriesCollection(intField).Name = "=Blad1!$" & Chr(65 +
intField) & "$3"
.Chart.SeriesCollection(intField).Values = "=Blad1!$" & Chr(65
+ intField) & "$4:" & Chr(65 + intField) & CStr(UBound(varTabel, 2) + 4)
.Chart.SeriesCollection(intField).XValues = "=Blad1!$A$4:A" &
CStr(UBound(varTabel, 2) + 4)
' .Chart.SeriesCollection(intField).Shape.Line.Weigh t = 1
Next intField
End If