mellowe said:
Basically I have simplified the problem...
I will probably have about 10 graphs but I want just to click on the
one graph and for it to refresh and show the data I require -
potentially it will be a user tool to others and I dont want to have
loads of graphs, just one so each user group can click on the their
option to supply only their data in a graph .. is this possible?? thnx
Please quote enough context to be able to tell what you are
referring to.
Now you've increased from 3 graphs to 10, and you want it to be
a "user tool" whatever that means.
First, estimate for yourself how much work you are prepared
to put into this. How fancy do you want it? How many bells
and whistles? Do you want a thing you can produce in ten
minutes work? (If so, use tabs to new sheets.) Or are you
prepared to spend much longer?
Sit down and write yourself a spec. (Or type it.) Make sure you
know what you want to accomplish in as much specific detail
as you can manage. If you are doing this for somebody, make
sure they have agreed that what you are suggesting is really
what they want. See if you can anticipate any changes that
people are likely to ask for in the near future, and then code so
that those changes will be easy. For example, you have asked
about 3 graphs and now 10. So it's fairly obvious that it's not
one specific number of graphs. What *exactly* and in detail
do you mean by "user tool?"
Spend an appropriate amount of time on the spec. For a project
you expect to take a couple hours, the spec should take a half
hour or so to whomp up. For a many month project, the spec
should take a month or two to prepare, and probably will get
updated during the project.
There are lots of ways you could go. For example, you could
put some buttons or other user controls on a graph, and have
these do stuff for you. You could arrange to have the currently
available graphs, their data source, titles, settings, etc., kept
in a table on a sheet only used for that purpose. Possibly hidden.
And you could spend a lot of time writing gorgeous VBA code
to run all this.
You could make something less grandiose. Maybe a button
that will start a VBA code that checks how many data sets are
on the graph, and offers you a form that allows you to check the
ones you want visible. Then the ones you select to be non-visible
are formatted to have point and line set to "none" so they are
not visible. Produces a fairly weird legend box, but whatever.
Or, the amount of effort you are prepared to expend might not
cover that. In which case, you are probably back to just tabs.
Socks
