Automating Selection of Graphs for Printing

[Susan Lund]

I have a workbook containing cost data for up to 20 client
plans. Each plan's data generates three different graphs,
which are collected on three separate worksheets. Thus,
sheet 1= 20 plans worth of data input, sheet 2= 20 graphs
(type 1), sheet 3= 20 graphs (type 2), and sheet 4= 20
graphs (type 3). Once the data is entered, any assorted
grouping of graphs might need to be printed at one time.
It has been a pain to scroll around and search for the
graphs needed for printing out. Since there are dozens of
the self-contained workbooks I've described, I'd like to
automate the selection and printing process. Just as a
quick visual reference, I created a table of contents
sheet which has title links of the 20 specific plans
included in that workbook. I would like to add 3 check
boxes to each of the 20 titles on the contents page, so a
user can check whichever of the 60 graphs they want to
print, and then somehow assign a "print" button that will
execute the selections. Is this even possible? Any ideas
on how to set this up? I currently have Excel 97 but am
upgrading to 2003 next week. Thanks in advance!
Susan

 

[Tushar Mehta]

I cannot think of any simple solution that would work without some VBA
support. With VBA, and assuming you have named each of the 60 charts
in each of the workbooks consistently, it should be possible.

Alternatively, and especially if the graphs of one particular type are
the same with differences only in the data plotted, instead of having
on a per type basis 20 graphs, one for each plan, why not have one
graph that adjusts itself for a particular plan? Something along the
lines of Dynamic Charts (http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/index.html), specifically
example 6.

Now, the user selects what to graph using a drop down box (or boxes as
the case may be) and then you unconditionally print the only three
graphs in the workbook!


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Thank you so much for your assistance. I think I am going
to have to figure out the VBA code to do what I need as
the graphs are indeed unique and would not be able to be
generated dynamically. However, that was superb info on
your website! It gave me lots of ideas for some other
projects I am working on, so thank you very much!

Susan