Solved

Insert Checkboxes linked to cells to indicate make graph

Posted on 2001-08-01
12
274 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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 150 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

How many times recently have you prepared a presentation or emailed a document to a client and you have found that they have older versions of MS Office and they can not open the file you have prepared.  Although most visitors to this site are exper…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

758 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