Multiple Active Worksheets
 

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!

Multiple Active Worksheets
 

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!

Multiple Active Worksheets
 

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!

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!