Controls on chart sheets

  • Thread starter Thread starter Del Cotter
  • Start date Start date
D

Del Cotter

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?
 
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
 
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.
Click to expand...

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.
Click to expand...

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.
 
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.
 
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.
Click to expand...

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?
 
Maybe somebody else has a different approach, but I would have used
INDIRECT and ADRRESS.

Cheers
Andy
 
Back
Top