Managing 70 checkboxes in financial graphing application

[The poster formerly known as Colleyville Alan]

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

 

[Jon Peltier]

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

 

[The poster formerly known as Colleyville Alan]

Thanks, Jon. 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 most of the code working except for one piece. I have a sub that
calls another sub that counts the number of boxes checked, pops up a message
box if the limit is exceeded and turns the checked value back to false if
the limit is exceeded. It mostly works, but there is a glitch.

The first sub is the CheckBox1_Click() event. 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. Unfortunately, in
my test code I only have 5 check boxes and the code always unchecks box #5.

It's been awhile since I played around with VBA, so I looked for some code
on the Internet and found a few snippets that looped through the controls on
a worksheet. I thought that there would be a more straightforward approach,
but I used this as a starting point. The problem is that I cannot get the
program to recognize that the text string name that I hard-coded for
Checkbox1 is the same as the Checkbox1.name property. I have tried several
ways, with double quotes, triple sets of double quotes, variables to hold
the name, etc, etc. The debug.print, locals, watch window, all show that
they are the same, but there are probably some implicit quotes or something
that I am missing.

Dim obj As OLEObject
Dim chkName As String
chkName = "Checkbox1"

For Each obj In ActiveSheet.OLEObjects
If obj.Name = chkName Then
MsgBox ("This works!")
End If
Next

Any ideas as to why the two names do not match when they both appear to in
the debug.print window?
Is there a more straightforward way to get and pass along the name of the
object, in this case Checkbox1, to the next sub?
Thanks

 

[The poster formerly known as Colleyville Alan]

Well, I got the code to work, but the question about assigning the CheckBox
directly to an object variable without going through a loop to test each
object still stands.
As always,
Thanks.
Alan

 

[Jon Peltier]

Did I mention my approach needs no VBA? It's much much simpler than what you
describe. If you want to group items in the listbox, you could but a line of
dashes as a list item, and if it gets selected, have it reference a blank
range, so nothing is plotted.

To pass along a name of a control, you could try using Application.Caller
from the first sub.

- Jon

 

[Jon Peltier]

My last response was a further attempt to get you to drop this complicated
approach, but I did include what I think you need:

Application.Caller.

- Jon

 

[Jon Peltier]

Heck, if you needed the groupings, you could use a two-tiered listbox
approach. Each potential series would have two listboxes. One would allow
selection of the group, the other would show the options within the group.
It could all be driven by names, and would still need no VBA.

Or you could use option buttons for selection of the group, and the listbox
would show the selections from the group.

- Jon

 

[The poster formerly known as Colleyville Alan]

Thanks for the info.
Alan

Heck, if you needed the groupings, you could use a two-tiered listbox
approach. Each potential series would have two listboxes. One would allow
selection of the group, the other would show the options within the group.
It could all be driven by names, and would still need no VBA.

Or you could use option buttons for selection of the group, and the
listbox would show the selections from the group.

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