Controls on chart sheets

I count myself a fairly competent user of Excel, but I've just started
expanding my knowledge thanks to some of the web sites out there
(including Jon Peltier's fine examples). I was delighted with Rob
Bovey's labelling add-in, which gives Excel something that I have missed
ever since my firm stopped using Lotus 123.

Now I am experimenting with controls in Excel, and I have added a scroll
bar to interactively change the appearance of an embedded chart. My
problem is that I don't usually use charts embedded in a spreadsheet; I
prefer to have them on their own pages. I was forced to do it this way
in my experiments because I don't seem to be able to add controls to
chart sheets.

Is there some trick I'm missing?

--
Del Cotter
Thanks to the overwhelming volume of UBE, I am now rejecting *all* email
sent to . Please send your email to del2 instead.

Hi Del,

The only trick you are missing is that the controls from the Control
Toolbox (activeX's I think) can not be added to a chart, sheet or embeded.
You have to use the Forms Controls if you want the controls in the chart.

Cheers
Andy

Del Cotter wrote:

I count myself a fairly competent user of Excel, but I've just started
expanding my knowledge thanks to some of the web sites out there
(including Jon Peltier's fine examples). I was delighted with Rob
Bovey's labelling add-in, which gives Excel something that I have missed
ever since my firm stopped using Lotus 123.

Now I am experimenting with controls in Excel, and I have added a scroll
bar to interactively change the appearance of an embedded chart. My
problem is that I don't usually use charts embedded in a spreadsheet; I
prefer to have them on their own pages. I was forced to do it this way
in my experiments because I don't seem to be able to add controls to
chart sheets.

Is there some trick I'm missing?


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

On Sat, 17 Apr 2004, in microsoft.public.excel.charting,
Andy Pope said:

The only trick you are missing is that the controls from the Control
Toolbox (activeX's I think) can not be added to a chart, sheet or
embeded.

Well, what I meant was that I had the control on the spreadsheet next to
an embedded chart so I could see changes in the chart as I manipulated
the control.

You have to use the Forms Controls if you want the controls in the chart.

Ah, that's much better. And I see Excel 95 has Forms as well, so the
spreadsheets I design at work will still work at home.

--
Del Cotter
Thanks to the overwhelming volume of UBE, I am now rejecting *all* email
sent to . Please send your email to del2 instead.

Okay, next question. I have 20 worksheets in a workbook, all the same
format but with different contents. I don't really want to have to
create and manage a chart for every worksheet.

What I'm looking for is to create one chart with an elegant way of
choosing which worksheet to look at, such as a list box from which the
user can select a sheet and have the chart present the data on that
sheet.

I'm not interested in pivot tables or pivot charts for this, as they
have limitations I'm not willing to work with.

--
Del Cotter
Thanks to the overwhelming volume of UBE, I am now rejecting *all* email
sent to . Please send your email to del2 instead.

Hi Del,

As a starting point take a look aat Jon Peltier's example.
http://peltiertech.com/Excel/Charts/ChartByControl.html

You probably need to create a single chart-data sheet where the content
is updated by some selection criteria.

Cheers
Andy

Del Cotter wrote:


Okay, next question. I have 20 worksheets in a workbook, all the same
format but with different contents. I don't really want to have to
create and manage a chart for every worksheet.

What I'm looking for is to create one chart with an elegant way of
choosing which worksheet to look at, such as a list box from which the
user can select a sheet and have the chart present the data on that sheet.

I'm not interested in pivot tables or pivot charts for this, as they
have limitations I'm not willing to work with.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

On Sun, 18 Apr 2004, in microsoft.public.excel.charting,
Andy Pope said:

Hi Del,

As a starting point take a look aat Jon Peltier's example.
http://peltiertech.com/Excel/Charts/ChartByControl.html

You probably need to create a single chart-data sheet where the content
is updated by some selection criteria.

Okay, that's sorted now, thanks.

One other thing, although it's not strictly a *chart* question. In
order to get a large amount of data from each sheet on to the sheet the
chart is reading, I used the following formula:

=INDIRECT(ADDRESS(ROW(),COLUMN(),4,,$A$1))

where A1 is the location of the cell where the sheet's name is updated
by the form control.

This works fine, but it looks a bit clunky to me, surely there's a less
Rube Goldberg way of saying the same thing?

Also, I tried to save processor time and file space by making this an
array formula, but Ctrl-Shift-Enter didn't work right, so I ended up
copying it into each individual cell. Any tips?

--
Del Cotter
Thanks to the overwhelming volume of UBE, I am now rejecting *all* email
sent to . Please send your email to del2 instead.

Maybe somebody else has a different approach, but I would have used
INDIRECT and ADRRESS.

Cheers
Andy

Del Cotter wrote:

On Sun, 18 Apr 2004, in microsoft.public.excel.charting,
Andy Pope said:

Hi Del,

As a starting point take a look aat Jon Peltier's example.
http://peltiertech.com/Excel/Charts/ChartByControl.html

You probably need to create a single chart-data sheet where the
content is updated by some selection criteria.


Okay, that's sorted now, thanks.

One other thing, although it's not strictly a *chart* question. In
order to get a large amount of data from each sheet on to the sheet the
chart is reading, I used the following formula:

=INDIRECT(ADDRESS(ROW(),COLUMN(),4,,$A$1))

where A1 is the location of the cell where the sheet's name is updated
by the form control.

This works fine, but it looks a bit clunky to me, surely there's a less
Rube Goldberg way of saying the same thing?

Also, I tried to save processor time and file space by making this an
array formula, but Ctrl-Shift-Enter didn't work right, so I ended up
copying it into each individual cell. Any tips?


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info