Auto-sizing a chart when its window resizes in Excel 2010 1

[N1GHTEYES]

I first posted this in the office forum, but Skip sensibly suggested I try here. So here goes...
This is driving me nuts.
I have an Excel workbook with several sheets, including chart sheets. I have set this up to show several of the sheets in their own windows, including the charts (and some of the rest - mainly as an "input area"). I need to let the user click a point on one of the charts, so that the workbook can detect the mouse down event and change the rest of the display accordingly. I have the code to do this.
I also want him to be able to rearrange the windows, including resizing them, as appropriate, to accomodate detailed views on his display of interest. This works fine in 2003, with the chart sheets set to "Sized to window", but fails when I use it in 2010, as the size to window option seems to be missing.
Having checked where Skip suggested, it seems that microsoft have removed this facility, whereby the chart stretches to fit the resized window as it changes (which is what I want) and replaced it with the zoom to fit button, which has to be selected manually by the user and always keeps the same aspect ratio.
So, does anyone have a workaround in code?
I had a look at the chart object. It does have a sizewithwindow property, but when I set this to true in code, although it does not throw an error, it also does not change the state of the property - which remains at "false".
So I thought I might be able to detect the window resize event and change the chart dimensions to fit the new size, but I'm not sure how to access the window resize event, or the chart dimensions.

Any suggestions greatly appreciated.

Thanks,
Tony
p.s. Hi guys - it's been a while...

 

[SkipVought]

Tony,

It just occurred to me that what you might be able to do is us the Window (Top, Left, Height, Width) parameters and use them to size and position the embedded chart.

For instance, I use, not Window parameters, but row parameters to resize the PLOT AREA of a bar chart (like a gantt) to, in effect, align each bar with a row of data, be it 2 or 200.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

There is a Window.OnWindow property that fires when the user clicks into a window.

But I don't see anything that fires when clicking out of a window.

One could imagine some obtuse code that might get there using .OnWindow - maybe.

 

[N1GHTEYES]

When you say window, you mean the window associated with the sheet on which the chart is embedded? If so, I'm not sure how I'd access that worksheet's window's resize event.
But if the chart was embedded, I could simply give the user control to resize the chart directly and he could rearrange things to his liking. So, I don't think I'd need the row parameters as a proxy to do it in code.
If I embed the chart, the problem comes when I want to trap the chart mousedown event.
Unfortunately, I can't experiment at the moment, because I'm at home working with '97 (yes, '97, really) and anything I do here is not guaranteed to work in 2010, which is what we have at work.
Also unfortunately, they have locked down the access at work to such a degree that they have banned pretty much all forums - including Tektips!!!
(don't get me started on that one)
That is one reason I have not been on here for ages.
So, if you, or anyone else can point me to some working code to capture clicks on embedded charts which works in 2010, or some means, in code, of replicating the auto-sizing behaviour of earlier charts sheets (when sized with window was set), that would be very helpful.


Thanks
Tony

 

[mintjulep]

I was thinking something like:

User clicks into the window of interest.

Window_of_Interest.OnWindow fires, running a procedure that records the current size and location of the window somewhere. Global variable, or cells on a hidden sheet for example.

User adjusts window size.

User clicks into some other window - since there doesn't seem to be a leave the window of interest event, this serves as a poor proxy.

Some_Other_Window.OnWindow fires, running a procedure that checks the current size of all other windows against their last recorded size.

If there is a difference, adjust the size of the embedded chart to match the current size.

As I said - obtuse.

 

[N1GHTEYES]

Mintjulep - sorry, it looked like my post was responding to yours, but actually it was in response to Skip's. Yours had not arrived on my screen when I replied. I guess we overlapped.

I think it might help if I gave a better example of the kind of thing I'm trying to do. The following is NOT what I'm actually doing (the details are a bit sensitive) but is is equivalent.

Lets say I have a workbook with three sheets, all of which are visible to the user in separate windows. On sheet 1, he can select, from drop-down boxes, a country, a year and a type of weather statistics (rainfall, sunlight hours, mean temperature etc...).
On Chart Sheet 1, it shows the selected statistic as a function of month, over the period of the selected year, for the chosen statistic in the selected country, say rainfall in Bolivia, in 2001.
When he clicks on a point on this chart, that selects a particular month, and the detailed stats per day for that month are shown on the second chart, on Chart sheet 2. So the sheet 2 chart now shows the details of the rainfall for May, say.
He can rearrange the windows as he sees fit.
I use separate windows so that the "clickable" chart is on its own sheet so that it exposes mouse down and mouse up events on which I can hang the code that displays the appropriate data on chart 2.
The use of the separate windows also allows him to resize and arrange the charts to suit his preference. When "sized with window" was available (in '97-2003), this worked nicely because as he resized the window the charts stayed filling the window. Now if he does this they stay at the same aspect ratio and only fill part of the window. This is what I want to fix. In other words, as the chart window is resized, I want to:
a) catch the resize event, and
b) note the new size of the window and actively resize the chart to fit it exactly.

Alternatively, I could have the drop-down boxes and the charts in a single worksheet, and he can thus move and size the charts to his heart's content. However, if I do that, I no longer have easy access to the chart mouse events.
I know there have been posts in the past addressing how to capture chart events with embedded charts, but I'm not sure if they still work in 2010, and I can't easily check them out at work because there I have no access to tektips, and at home I don't have access to 2010.

So the questions boil down to these:
1) is it still possible to access chart events in embedded charts in 2010, and / or
2) is it possible to actively find the size of the window of a clicked chart in 2010 and if so,
3) can I actively change the size of the chart, in the window, in code?

I hope that is a bit clearer.
Tony

 

[SkipVought]

Tony,

This is a 2013 link, but it look virtually identical to the documentation I've used sing 2003 for embedded charts.

http://msdn.microsoft.com/en-us/library/office/ff194070(v=office.15).aspx

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[N1GHTEYES]

Thanks Skip. It works a treat in '97.
Though I had to replace the line:
Charts(1).ChartObjects(1).Chart

with:
Worksheets(1).ChartObjects("Chart 1").Chart

because the global charts object was empty - despite having created a chart.

It also needed me to name the class "EventClassModule", which the instructions do not explicitly tell you to do.

However, once that was all sorted, it did the trick as far as embebdded charts are concerned.

So, I'll be able to test that out on Monday when I'm back at work.

Meanwhile, almost out of academic interest, does anybody happen to know how to do it the other way? That is, capture the window resize event for a chart sheet and actively take control over the chart size so as to mimic the "Sized with window" behaviour in code?