Re: Managing 70 checkboxes in financial graphing application

A simple way around the legend issue is to add a data label to the last
point of each series. This is generally better than a legend, because the
label's right where the data is. Also, if the series isn't shown (#N/A) then
neither is the label.

What I've done, which also sounds easier than what you are fighting with, is
use a separate range which has the magic number of columns (your seven) and
the same magic number of listboxes. The listboxes are each populated with
the list of possible series. Each of the columns in this plotting range uses
index functions to get data from the appropriate column, corresponding to
the selection in the listbox. Since there are only seven listboxes, and
seven plotted columns, you don't have to worry about the user checking an
8th box.

This is an extension of the first technique here, only using a listbox
instead of a dropdown:

http://peltiertech.com/Excel/Charts/ChartByControl.html

Listboxes are easier for users, because more options are visible without
having to drop down the list.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"The poster formerly known as Colleyville Alan" <nospam@xxxxxxxxxx> wrote in
message news:481d028b$0$3366$4c368faf@xxxxxxxxxxxxxxxxx
I showed my boss a simple graph with 6 checkboxes that allowed a user to
show or to hide data. He liked this alot and asked if I could build
something with many checkboxes. I said sure and after we surveyed my
colleagues, we came up with 70 different data series. My challenge now is
to manage how the checkboxes interact with the data and the graph.
(note: I am a finance guy and not a programmer, though I can write some
VBA).

I have a tab with the data series called "Data". Another tab has formulas
and is called Formulas. This tab references the data series on the Data
tab. For example:
IF(ISBLANK(Data!B7),NA(),IF(Main!$S$2,Data!B7,NA()))
This allows the graph to show blanks where data is missing as some series
have longer history than others.

I had toyed with the idea of adding/deleting series from the graph
depending upon the state of the checkbox. But I came up with an idea that
seems more manageable.
There is now a 3rd tab called Lookup that has seven series using a Hlookup
table to pull info from the Formulas tab. It looks at the name of the
data series and pulls that info to this tab. The graph then has seven
series that reference these cells. All I have to do is blank out the name
of the data series at the top of the column and all elements become #NA.

One of the reasons that I went to this approach is that while unchecking a
box that was linked to a series caused the series to disappear from the
graph, the legend still had the symbol for it. If I had 70 series and
only 2 boxes were checked, then I would show 2 series on the graph but
have all 70 series in the legend, 2 with names and 68 without.

My idea is to read the status of all checkboxes into an array. The
Checkbox_Click event calls a sub that counts the number of boxes checked.
If there are already 7 checkboxes selected and the user attempts to check
an 8th, the code would not allow that and would notify the user. (not that
anyone would be that silly, such a graph would be unreadable). If there
are less than 7 boxes checked, the code would grab the name of the checked
boxes from the array and write them to the Lookup tab in the column
headings used by the Hlookup function.


My question then becomes: is this a reasonable approach or is there a more
efficient way to do this? I thought about creating a fixed array of 7
elements scanning the controls and getting the first 7 and using them. If
the array was full and the user attempted to add an 8th checkbox, again it
would prevent that from happening. So since this array would never go
over 7 elements, would I really need to keep the status of all 70
checkboxes in an array of size 70?

Perhaps I should use a Dynamic Array and check the size with Ubound?

Any other ideas for managing this many checkboxes? I'm sure that I am not
the first guy to do this.
Thanks in advance.

Alan


.

Relevant Pages

  • Managing 70 checkboxes in financial graphing application
    ... I showed my boss a simple graph with 6 checkboxes that allowed a user to show or to hide data. ... I have a tab with the data series called "Data". ... If there are less than 7 boxes checked, the code would grab the name of the checked boxes from the array and write them to the Lookup tab in the column headings used by the Hlookup function. ...
    (microsoft.public.excel.programming)
  • Re: Managing 70 checkboxes in financial graphing application
    ... checkboxes was the "one too many" and uncheck that box. ... seven) and the same magic number of listboxes. ... I have a tab with the data series called "Data". ... My idea is to read the status of all checkboxes into an array. ...
    (microsoft.public.excel.programming)
  • Re: Managing 70 checkboxes in financial graphing application
    ... Jon Peltier, Microsoft Excel MVP ... of the checkboxes was the "one too many" and uncheck that box. ... I have a tab with the data series called "Data". ... My idea is to read the status of all checkboxes into an array. ...
    (microsoft.public.excel.programming)
  • Re: Managing 70 checkboxes in financial graphing application
    ... I took a look at that method and while it does look interesting, the layout of the checkboxes in related groups is something that my boss and my colleagues are looking for. ... I have a tab with the data series called "Data". ... This allows the graph to show blanks where data is missing as some series have longer history than others. ... If there are less than 7 boxes checked, the code would grab the name of the checked boxes from the array and write them to the Lookup tab in the column headings used by the Hlookup function. ...
    (microsoft.public.excel.programming)
  • Re: Managing 70 checkboxes in financial graphing application
    ... All I want this to do is to call the other sub and pass along the name of the checkbox that has just been checked so that after the counting is done, it will know which of the checkboxes was the "one too many" and uncheck that box. ... I have a tab with the data series called "Data". ... This allows the graph to show blanks where data is missing as some series have longer history than others. ... If there are less than 7 boxes checked, the code would grab the name of the checked boxes from the array and write them to the Lookup tab in the column headings used by the Hlookup function. ...
    (microsoft.public.excel.programming)