Chart syntax

[Arne Hegefors]

I have a syntax probelm. First I have a loop that checks to see if an object
in the selection is a chart. If that is so it passes the chart as an argument
to a sub that makes changes to that chart. The problem is that I do not
really know how to write the code.

The code looks like:

Sub arrayLoop()
Dim obj As Object
Dim myChart As Excel.Chart
For Each obj In Selection
If TypeName(obj) = "ChartObject" Then
Set myChart = obj
Call linjeDiagramKnapp(myChart)
End If
Next
end sub

Sub linjeDiagramKnapp(argChart As Excel.Chart)
Dim mySize As DoubleWith argChart
with argChart

..ApplyCustomType ChartType:=xlUserDefined, TypeName:="Standard"

With argChart
..Height = 150
..Width = 150
..Top = 100
..Left = 100
End With

With argChart.PlotArea
..Height = 100
..Width = 100
..Top = 10
..Left = 10
..Interior.ColorIndex = xlNone
End With

The problem is:

1) I get "incompatible types" at the line "Set myChart = obj".

2) Assuming that I fix problem 1) how shall I refer to the chart? Eg if I
want to change the plot area as in the last piece of code, is the current
syntax correct? If not how shall I write it?



Please, please help me out here! I know that I am a pain but I really cannot
solve it! Thank you so much for all your help! Any assistance always
appreciated!

 

[Jim Cone]

I would try...

Set myChart = obj.Chart
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Arne Hegefors"
wrote in message
I have a syntax probelm. First I have a loop that checks to see if an object
in the selection is a chart. If that is so it passes the chart as an argument
to a sub that makes changes to that chart. The problem is that I do not
really know how to write the code.
The code looks like:

Sub arrayLoop()
Dim obj As Object
Dim myChart As Excel.Chart
For Each obj In Selection
If TypeName(obj) = "ChartObject" Then
Set myChart = obj
Call linjeDiagramKnapp(myChart)
End If
Next
end sub

Sub linjeDiagramKnapp(argChart As Excel.Chart)
Dim mySize As DoubleWith argChart
with argChart

..ApplyCustomType ChartType:=xlUserDefined, TypeName:="Standard"

With argChart
..Height = 150
..Width = 150
..Top = 100
..Left = 100
End With

With argChart.PlotArea
..Height = 100
..Width = 100
..Top = 10
..Left = 10
..Interior.ColorIndex = xlNone
End With

The problem is:
1) I get "incompatible types" at the line "Set myChart = obj".
2) Assuming that I fix problem 1) how shall I refer to the chart? Eg if I
want to change the plot area as in the last piece of code, is the current
syntax correct? If not how shall I write it?
Please, please help me out here! I know that I am a pain but I really cannot
solve it! Thank you so much for all your help! Any assistance always
appreciated!

 

[Arne Hegefors]

Hello! Itried that but there is no such thing as obj.Chart in VBA. Thank you
very much anyway! If you have any other idea how to get this to work please
help me out! Thanks alot!

"Jim Cone" skrev:

 

[Jim Cone]

Sub arrayLoop()
Dim obj As Object
Dim myChart As Excel.Chart
For Each obj In ActiveSheet.ChartObjects
If obj.Name = "Chart 1" Then
Set myChart = obj.Chart
MsgBox myChart.Name
End If
Next
End Sub

-or-

Sub arrayLoop2()
Dim myChart As Excel.Chart
Set myChart = ActiveSheet.ChartObjects(1).Chart
MsgBox myChart.Name
End Sub

--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




"Arne Hegefors"
<[email protected]>
wrote in message
Hello! Itried that but there is no such thing as obj.Chart in VBA. Thank you
very much anyway! If you have any other idea how to get this to work please
help me out! Thanks alot!
"Jim Cone" skrev:

 

[NickHK]

Arne,
"obj" will a ChartObject, hence the True value of you If test.
But your sub is expecting a Chart, not a ChartObject.
So you need to pass that that object.
Call linjeDiagramKnapp(myChart.Chart)

You can check these objects and their various properties/methods etc in the
Object browser.
Normally there's an like a box with some shapes above on your menu bar.
Click that, make sure it says <All libraries> in the top combo and enter
your text (e.g. ApplyCustomType, no spaces) in the second combo and the
serach binoculars.
You then see a list that matches.

NickHK