Link to home
Start Free TrialLog in
Avatar of sluggo050499
sluggo050499

asked on

Insert Checkboxes linked to cells to indicate make graph

I have a Macro which systematically graphs grouped data on a worksheet.  It basically has a start row, number of rows to go down, and number of columns.  It also knows the number of groups which = the number of charts to make.

However, If I export over 255 "groups" it's too much to create seperate graphs for each.  Right now, I just have a message that there are too many variables.

What I would like to do is to Insert a Column (A) called "Graph" and put a check box at each group start cell.  If the checkbox is checked, the linked cell is "TRUE" and the group is graphed.

This is what I need to find out:  How to add a Checkbox into a cell, link it to the cell.  If I just run a macro to add a checkbox:

ActiveSheet.CheckBoxes.Add(8.25, 67.5, 24, 17.25).Select
    ActiveSheet.Shapes("Check Box 2").Select
    Selection.Characters.Text = ""
    With Selection
        .Value = xlOff
        .LinkedCell = "$A$5"
        .Display3DShading = False
    End With

I get x,y coordinates.  Is there a way to put it in a cell rather than x,y?
Avatar of sebastienm
sebastienm

I deal with quite the same thing. The main difference
is that I put ComboBoxes on the (only one) Chart for
the user to make a choice (In your case the COmboBox
would contain the Group NAmes)

Example:

To get the same within vba, put the macro recorder on,
and do the example then modif the code.
(maybe there are a few mistakes below but that's the idea)

In hidden sheet 'Data': revenue data by group name, month
--------------
GrpName Jan Feb March
 grp1   12  54   65
(...)

range HeaderRow+Data is named Data
range of group name (without header) is named Groups
(can be done with macro if dynamic)

In a hidden sheet 'Manager':
----------------------------

A1 is named Choice (will contain the user choice)

A2: Jan
B2: Feb
C2: March

A3: = offset(Data,1+Choice,1,1,1)
B3: = offset(Data,1+Choice,2,1,1)
C3: = offset(Data,1+Choice,3,1,1)

A2:C3 is named ChartData

in chart sheet Chart:
----------------------
make your graph with source being ChartData
from the Forms Toolbar (menu View>Toolbars>Forms),
put a combobox on the chart.
  in the Property dialog box:
   - Input Range is namedrange Groups
   - CellLink is Choice

Result
--------
The combo box is populated with the Group Names.
The user chooses a group name directly from the
chart (combox box)--> the result is sent to cell
'Choice' --> range 'ChartData' is recomputed
--> the chart is updated.

---------------------------------
This was a simple case where a group data is on
a single row.
When one group data is on several rows:
- use a macro to create a list of unique
  group name, put it in sheet Manager, and
  call the range 'Groups' (instead of being
  the column of groups in sheet Data)
- to get groupname: =index(Groups,Choie)
- chart computation:
   use the sumif function for computations.


Regards,
Sebastien
Avatar of sluggo050499

ASKER

That is a great idea.  I like it!

I can add the dropdown fairly easily with code:

ActiveSheet.DropDowns.Add(4436370.75, 25179.75, 1940610, 159837.75).Select
    With Selection
        .ListFillRange = "Sheet2!$B$3:$B$11"
        .LinkedCell = "Sheet2!$B$3:$B$11"
        .DropDownLines = 8
        .Display3DShading = False
    End With

Let me get some work going and I'll follow up with some questions.
1.  Is there an additem event I can do to fill the combo box?  ie.  If I know that every 8th row will have the group name in column 1 for 10 rows, what is the code to populate the combo box.

2.  When the user selects the variable in the combo box, how do I relate that to the group of cells to base the chart on?

3.  Is there an onchange event for the combo box to repopulate the chart with the new data?

If you have a sample worksheet, I'd appreciate it... please send it to doug@payraisecalculator.com

Thanks
Doug
sluggo,
I haven't had time to look at it so far. I'll do that asap.
quickly:
1.
In an other sheet (say sheet GroupInfo), put your unique group names programmatically . Name the range containing the group names (say GroupNames = A1:A10). Then:
   .listFilRange="=GroupInfo!GroupNames"
By naming the range, when you expand it or reduce it or change its content, .listFillRange will always adapt automatically: no coded needed, the box updates.

Name also your .LinkedCell , say B1 = UserChoice

2. Depends on what you are trying to graph and how your list is organized.
   Say  'Revenue sheet'
   A       B     C    D
1  Group  1999  2000 2001
2  AAA      54   45  45
3  BBB      454  24   47

in sheet GroupInfo
  C1: =index(GroupNames,UserChoice)  ---> returns group name

  Starts in D5:
D5: Group
E5: 1999
F5: 2000
G5: 2001
D6: =UserChoice
E6: =sumif(Revenue!A2:A150,$D6,Revenue!B2:B150)
F6: =sumif(Revenue!A2:A150,$D6,Revenue!C2:C150)
F6: =sumif(Revenue!A2:A150,$D6,Revenue!D2:D150)

ie
     D            E     F     G
5    Group        1999  2000  2001
6    =UserChoice  =sumif(Revenue!A2:A150,$D6,Revenue!B2:B150)  ...
   
Set your Graph souce to D5:F6


You can send me your sheet if you have problems:
sebastienm@etm.pdx.edu

Regards,
Sebastien
The sheet I have is automated and does a number of macros.  I'm unable to send it to you since it deals with sensitive/protected material and I'm unable to distribute it.  

However - the scenario you propose is very possible.

The entire workbook is built by macros.  Variables are analysed and each worksheet is built performing several tasks.  The entire contents are totally dynamic - I may have one group of variables with 6 subsets, or I may have 25 with 2 subsets in each.  The number of columns is just as dynamic.

Sooo... here's the difficulty and delay.  I need to basically read the data sheet (the export into Excel), and using the exported totals (A1=Number of Variables,C1=Number of Subsets in each variable, D1-1=Number of Columns).

A3 is where the data starts.  So my combo box must be filled with A3, (A3 down (C1+1) rows, for 31 times.  Then the cells which must be charted are B3, down C1 rows, over D1+1 columns).  Does that make sense?

Example:
A1=3,C1=5,D1=5

V1|SV1|100|Q1|Q2|Q3|Q4
  |SV2|100|Q1|Q2|Q3|Q4
  |SV3|100|Q1|Q2|Q3|Q4
  |SV4|100|Q1|Q2|Q3|Q4
  |SV5|100|Q1|Q2|Q3|Q4

V2|SV1|100|Q1|Q2|Q3|Q4
  |SV2|100|Q1|Q2|Q3|Q4
  |SV3|100|Q1|Q2|Q3|Q4
  |SV4|100|Q1|Q2|Q3|Q4
  |SV5|100|Q1|Q2|Q3|Q4

V3|SV1|100|Q1|Q2|Q3|Q4
  |SV2|100|Q1|Q2|Q3|Q4
  |SV3|100|Q1|Q2|Q3|Q4
  |SV4|100|Q1|Q2|Q3|Q4
  |SV5|100|Q1|Q2|Q3|Q4

In my combo box, I want V1,V2,V3.  But the referenced cells for the chart for each would be the:
SV1|100|Q1|Q2|Q3|Q4
SV2|100|Q1|Q2|Q3|Q4
SV3|100|Q1|Q2|Q3|Q4
SV4|100|Q1|Q2|Q3|Q4
SV5|100|Q1|Q2|Q3|Q4 for each

Whew... does that make sense?

I believe I can use the .additem command to add each of the Variables and use (Cells(down,right) to get the next variable referencing the contents of A1 and C1.

Then I can get the cell references for the chart using the relative location of my Variable and going down C1 rows and over D1+2 columns.

Second whew... do you see what I'm trying to do?  You've done quite a bit for me and I'm not pushing you for more - of course I never turn down help!!!

Sluggo
(I said 31 cause that's the example I was looking at... it could be any number residing in the A1 cell, sorry if I confused)
sebastienm: Hope you didn't quit on me!  Do you need more points?
ASKER CERTIFIED SOLUTION
Avatar of sebastienm
sebastienm

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I know what it's like, sebastienm!  There's no rush - I just wanted to make sure nothing was wrong.  You can send it to douglaskarr@msn.com

Thanks!
douglaskarr, gosh that name is familiar. Haven't I seen that at *some other* site?

LOL

Anyway, sluggo, can you let me know what's up with this question?

And, sebatienm, you *promised*. LOL

amp
Community Support Moderator
Oops... talking about letting something slide!

Another 50 for the wait!

Doug
Nice job, Doug!