• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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?
  • 7
  • 3
  • 2
1 Solution
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)


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

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.

sluggo050499Author Commented:
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.
sluggo050499Author Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

I haven't had time to look at it so far. I'll do that asap.
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:
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)

     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:

sluggo050499Author Commented:
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?





In my combo box, I want V1,V2,V3.  But the referenced cells for the chart for each would be the:
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!!!

sluggo050499Author Commented:
(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)
sluggo050499Author Commented:
sebastienm: Hope you didn't quit on me!  Do you need more points?
Sorry, I have been completely overhelmed during the past weeks.
I'll make a demo workbook Sunday...I promise...

sluggo050499Author Commented:
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

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


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

And, sebatienm, you *promised*. LOL

Community Support Moderator
sluggo050499Author Commented:
Oops... talking about letting something slide!

Another 50 for the wait!

Nice job, Doug!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now