Insert Checkboxes linked to cells to indicate make graph

Posted on 2001-08-01
Last Modified: 2011-09-20
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?
Question by:sluggo050499
  • 7
  • 3
  • 2
LVL 16

Expert Comment

ID: 6342812
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.


Author Comment

ID: 6342965
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.

Author Comment

ID: 6347165
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

LVL 16

Expert Comment

ID: 6369667
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:


Author Comment

ID: 6374981
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!!!


Author Comment

ID: 6374984
(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)
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 6423479
sebastienm: Hope you didn't quit on me!  Do you need more points?
LVL 16

Accepted Solution

sebastienm earned 150 total points
ID: 6423565
Sorry, I have been completely overhelmed during the past weeks.
I'll make a demo workbook Sunday...I promise...


Author Comment

ID: 6424684
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


Expert Comment

ID: 6709050
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

Author Comment

ID: 6709401
Oops... talking about letting something slide!

Another 50 for the wait!


Expert Comment

ID: 6711476
Nice job, Doug!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Modify default height of drop-down filter menu in Excel 2 45
Oart.dll 2 44
how to open abcd.txt.orig file? 10 44
LEFT JOIN Access Query 5 56
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now