I have a sheet that contains about 32 charts, since there are so many
charts I reduced the scale of the sheet so I can see all charts.
Problem is the charts are so small I can't interpret them. I want to
create a macro that re-sizes them when I click on them without having
to write a separate macro for each chart. Is there a way to return the
name of a chart to a variable? Here is what i have so far:

Dim ChtOb As ChartObject


With ActiveSheet

Set ChtOb = .ChartObjects("chart 1")


If ChtOb.Height < 1500 Then
With ChtOb
.Height = 1500
.Width = 2800
.Top = 34
.Left = 43
End With
Else
With ChtOb
.Height = 420
.Width = 715
.Top = 5
.Left = 5
End With
End If


--
Eric_B
------------------------------------------------------------------------
Eric_B's Profile: http://www.excelforum.com/member.php...o&userid=24486
View this thread: http://www.excelforum.com/showthread...hreadid=540641

Right click on each chart, select Assign Macro, then select the name of this
macro.

Then put this in the macro:

Set ChtOb = .ChartObjects(Application.Caller)

When you run this macro, you might first determine app.caller's size, then
have the macro loop each chart in the sheet, and if it is larger than X
apply the smaller dimensions, then find the app.caller and if its original
size was small, increase its size.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Eric_B" wrote in
message ...

I have a sheet that contains about 32 charts, since there are so many
charts I reduced the scale of the sheet so I can see all charts.
Problem is the charts are so small I can't interpret them. I want to
create a macro that re-sizes them when I click on them without having
to write a separate macro for each chart. Is there a way to return the
name of a chart to a variable? Here is what i have so far:

Dim ChtOb As ChartObject


With ActiveSheet

Set ChtOb = .ChartObjects("chart 1")


If ChtOb.Height < 1500 Then
With ChtOb
Height = 1500
Width = 2800
Top = 34
Left = 43
End With
Else
With ChtOb
Height = 420
Width = 715
Top = 5
Left = 5
End With
End If


--
Eric_B
------------------------------------------------------------------------
Eric_B's Profile:
http://www.excelforum.com/member.php...o&userid=24486
View this thread: http://www.excelforum.com/showthread...hreadid=540641