Increase/decrease chart size on double-click

[ChrisXcel]

I would like to be able to have my charts increase/decrease their size by a factor of two when I double-click on them.
So the first double-click would double its size and the second double-click would return its size to its original.

But the macro would need to keep track of each chart, because I have more than a dozen, and it would need to remember
which state/size each chart was in.

Can anybody help me out with this?

[Joske920]

Hi ChrisXcel,

Maybe something like this:

Please Login or Register  to view this content.

you can assign the macro to the chart itself and it will be a single click instead of double, double opens the property menu


Joske

[ChrisXcel]

Hi ChrisXcel,

Maybe something like this:

Please Login or Register  to view this content.

you can assign the macro to the chart itself and it will be a single click instead of double, double opens the property menu


Joske

Thanks for helping Joske!

I tried your code but there are a few problems right now.

First of all, I would need to know the chart name of each of my charts for the code to work.
The charts are deleted each day with new ones replacing them.

Secondly, when I ran the code manually after changing the starting sizes (width and height) to what
was assigned to the chart, the code jumped to the 'Else' section, without doubling the chart size!

[ChrisXcel]

The following code worked to make one chart bigger:

Please Login or Register  to view this content.

But here are the problems:

1. I had to know the name of the chart beforehand "Chart 599", for it to work.
2. The names of the charts change each time the VBA code is run each day with new data.
The VBA code creates new charts each day with the new names.

[Andy Pope]

If you want to use the doubleclick event you will need to use a class object to enable the capture of the event for dynamic objects.

Once the newly create charts are available run the code to create a collection of object to capture the event and resize the chart

Module code

Please Login or Register  to view this content.

class CChtEvt code

Please Login or Register  to view this content.

If your charts are on multiple sheets or actual chart sheets the same class code can be used but you will need to extend the EnableAllChartEvents routine to find the relevant charts.

[ChrisXcel]

If you want to use the doubleclick event you will need to use a class object to enable the capture of the event for dynamic objects.

Once the newly create charts are available run the code to create a collection of object to capture the event and resize the chart

Module code


class CChtEvt code

If your charts are on multiple sheets or actual chart sheets the same class code can be used but you will need to extend the EnableAllChartEvents routine to find the relevant charts.

Hi Andy thanks very much for helping me out.

Your code is working beautifully!

There's just one little problem that hopefully you can fix for me.
When I double click on a chart it doubles in size, as desired and when I double click again it goes back to the original size.
That's working great.

But at the same time, a box pops up with the following info:

Format Chart Area:
Patterns/Font/Properties
OK/Cancel

This is the Format box that would pop up normally without your code, when you double-click on any chart.

How can I suppress that pop-up?

[ChrisXcel]

Well I found a temporary fix/workaround.
I inserted a SendKeys ("{ESC}") command at the end of the code.

It takes care of the pop-up box (gets rid of it).
However there is a bit of an annoying ghost blinking that occurs when this happens.

I tried to take care of that with
Application.ScreenUpdating = False
Application.ScreenUpdating = True

But that hasn't helped.

[Andy Pope]

Just set the Cancel argument in the event to True

Please Login or Register  to view this content.

[ChrisXcel]

Just set the Cancel argument in the event to True

Please Login or Register  to view this content.

Thanks very much Andy!
That seems to have done the trick.