PDA

View Full Version : Solved: "Pick and choose" series to display on embedded chart



mae0429
06-30-2008, 12:35 PM
Hi all,

I've got some data that when all is said and done, it consists of 6 groups of 6 ranges (with variability in the number of cells in those ranges). Currently, I'm displaying them as 36 separate charts on the same worksheet. Is it possible to add 6 checkboxes to a chart that would allow for selection of a range to make a series? This way, I could narrow this to 6 charts, making it a little more useful to my end user.

I started on it some, but obviously I cannot refer to the chart with the object "Frame1" because it won't support that property. Any ideas how to go about this? (I don't want the whole "user form w/ picture" solution, please)

Here's what I've tried (with all the necessary declarations):

Set Frame1 = Worksheets(activeShtName).ChartObjects(a)
For idx = 1 To 6
Set chk = Frame1.Controls.Add("Forms.CheckBox.1", "CheckBox" & idx, True)
chk.Left = 5
chk.Top = (idx - 1) * (chk.Height + 2)
chk.Caption = "CheckBox " & idx
Next idx




Thanks!

-Matt

figment
06-30-2008, 03:37 PM
my first suggestion would be to use one graph with 6 buttons and a pull down menu, but if you have to have 6 graphs showing, then make all the buttons manually the write the code for what they do, rather then trying to make the buttons with code.

mae0429
07-01-2008, 06:35 AM
The whole process has to be done "automatically" so I'm going to need to hard code it: We'll have multiple data sets coming in (and by multiple I mean lots and lots and lots) and there's no way I'm going to make all those buttons manually.

figment
07-01-2008, 06:45 AM
in that case i would make a template file, with the graphs and the buttons, then use code to import the data sets into the template, and save it as a different name, this way you only have to make the buttons and the graphs once.

mae0429
07-01-2008, 06:54 AM
Darn, a template was going to be my backup plan...well, I'll get to work on that, but I'm going to leave this thread "unsolved" for the rest of the day to see if anyone can solve the coding of checkboxes onto a chart. If no response, I'll close it when I leave work.

mae0429
07-01-2008, 07:13 AM
From Andrew Poulsom:



To position a CheckBox from the Forms Toolbar at the top left of a Chart:



Sub Test()
Dim ChObj As ChartObject
Set ChObj = ActiveSheet.ChartObjects(1)
ActiveSheet.CheckBoxes.Add ChObj.Left, ChObj.Top, 81, 17.25
End Sub