Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Insert Checkboxes linked to cells to indicate make graph

Posted on 2001-08-01
12
Medium Priority
?
286 Views
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?
0
Comment
Question by:sluggo050499
  • 7
  • 3
  • 2
12 Comments
 
LVL 16

Expert Comment

by:sebastienm
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)

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
0
 

Author Comment

by:sluggo050499
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.
0
 

Author Comment

by:sluggo050499
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 doug@payraisecalculator.com

Thanks
Doug
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Expert Comment

by:sebastienm
ID: 6369667
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
0
 

Author Comment

by:sluggo050499
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?

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
0
 

Author Comment

by:sluggo050499
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)
0
 

Author Comment

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

Accepted Solution

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

Sebastien
0
 

Author Comment

by:sluggo050499
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 douglaskarr@msn.com

Thanks!
0
 

Expert Comment

by:amp072397
ID: 6709050
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
0
 

Author Comment

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

Another 50 for the wait!

Doug
0
 

Expert Comment

by:amp072397
ID: 6711476
Nice job, Doug!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

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 …
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

810 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