View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
NickHK[_3_] NickHK[_3_] is offline
external usenet poster
 
Posts: 415
Default Multiple Active Worksheets

Arne,
Which line do you get that error ?
For one thing .ApplyCustomType is a method of the Chart object, not the
ChartObject.
So you could pass
Call linjeDiagramKnapp(obj.Chart)
or
With argChart.Chart

Also, I don't understand your error handling, as you turn on the error trap,
then turn it off immediately.
On Error GoTo ChartErrorHandler
On Error GoTo 0


What is your module actually called, because
..OnAction = "ChartModul1.arrayLoop"
but also you have
In Module1:

NickHK

"Arne Hegefors" ...
Hello NickHK! Thank you very much for your help. However I seem to have
some
trouble implementing your suggestion. When trying to use a selection of
more
than one chart and using your code I get the error message: "Argument is
not
optional".

My code in MainModule is:

.......
.OnAction = "ChartModul1.arrayLoop"
.....

In Module1:

Sub arrayLoop()
Dim obj As Object

'loopar igenom alla selekterade objekt
For Each obj In Selection
If TypeName(obj) = "ChartObject" Then
Call linjeDiagramKnapp(obj)
End If
Next

End Sub

Sub linjeDiagramKnapp(argChart As Object)
'Dim objChart As ChartObject
Dim mySize As Double
On Error GoTo ChartErrorHandler
On Error GoTo 0
With argChart
.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Standard"

Please if you know what is wrong help me out! I know I have been asking
alot
of questions and I am utterly grateful for your help! Any assistance
appreciated! Thank you very much in advance!

"NickHK" skrev:

Arne,
You can loop through the "Selection", checking if you have any
ChartObjects.
Also, change your SizeTheChart to accept an argument of a ChartObject, so
you can pass any found in the loop :
Private Sub CommandButton1_Click()
Dim obj As Object

For Each obj In Selection
If TypeName(obj) = "ChartObject" Then
Call SizeTheChart(obj)
End If
Next
End Sub

Sub SizeTheChart(argChart As ChartObject)
Dim mySize As Double
With argChart
.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Standard"
....etc

NickHK

"Arne Hegefors" ...

I have a small macro that produces a floating toolbar. When you select a
chart and then press a button on the toolbar the chart configuration is
set
according to speciments in the code. However I would like to be able to
select many charts and then just press the button. Now i have to do the
same
process for every chart.

My code looks like:

Sub SizeTheChart()
Dim objChart As ChartObject
Dim mySize As Double
With ActiveSheet
ActiveChart.ApplyCustomType ChartType:=xlUserDefined,
TypeName:="Standard"

With objChart
.Height = 150
.....etc. Please help me how you can refer to multiple charts if
possible.
Thank you very much in advance. Any help appreciated!