Multiple Active Worksheets

[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!

 

[NickHK]

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]

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:

 

[NickHK]

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