"Pick and choose" series to display on embedded chart

[mae0429]

Hi all,

I posted this in the VBA Express forum yesterday:
http://www.vbaexpress.com/forum/showthread.php?t=20540

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)

It hasn't been addressed really, and I would like to know if this is possible to do by hard-coding. The charts are created at run-time and I don't want to have to go in after the fact to create buttons everytime this is run (which is going to be a lot...plus, it's not going to be me who's running the macro).


The code I've been trying to use for creating the checkboxes is:

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

 

[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

 

[mae0429]

Beautiful! Thank you!

-Matt