Re: Managing 70 checkboxes in financial graphing application

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, 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:481e23cc$0$12948$4c368faf@xxxxxxxxxxxxxxxxx
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

"The poster formerly known as Colleyville Alan" <nospam@xxxxxxxxxx> wrote
in message news:481e09da$0$5108$4c368faf@xxxxxxxxxxxxxxxxx
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 work***. 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 Active***.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




"Jon Peltier" <jonxlmvpNO@xxxxxxxxxxxxxxxxxxx> wrote in message
news:eLREqhZrIHA.420@xxxxxxxxxxxxxxxxxxxxxxx
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






.

Loading