Refer to Grouped Charts (or anything that's grouped) in VBA

[noname91]

Hello all,

I'd just like to quickly ask how one can refer to grouped objects, both in general and for charts in particular, in VBA?


I have the following for referring to single charts:

Sub Picture1_Click()
    ActiveSheet.ChartObjects("Chart 6").Visible = True
    ActiveSheet.ChartObjects("Chart 9").Visible = True
    ActiveSheet.ChartObjects("Chart 7").Visible = False
    ActiveSheet.ChartObjects("Chart 8").Visible = False
End Sub
Private Sub CommandButton2_Click()
    ActiveSheet.ChartObjects("Chart 6").Visible = False
    ActiveSheet.ChartObjects("Chart 9").Visible = False
    ActiveSheet.ChartObjects("Chart 7").Visible = True
    ActiveSheet.ChartObjects("Chart 8").Visible = True
End Sub

Now this obviously looks tedious but grouping them should help make it more easier to manage. But I can't seem to find the appropriate function for a group of charts anywhere online. I have tried a few combinations including ChartGroups (with the Group name in the brackets) and GroupItems, but each time I keep getting the following error message:

"Object doesn't support this property or method"


Any help would be appreciated. Thanks!

[p45cal]

After you've grouped a bunch of charts on the sheet, leave the group selected and see what the group's name is in the top left of the screen in the Names field (to the left of the formula bar where it normally shows the address of the active cell). You can also find the name in the Immediate pane of the VBE if, while the group is still selected, you type ?selection.name and press Enter.
Let's say it turns out to be Group 8, then you can use it in VBA thus:

  ActiveSheet.Shapes("Group 8").Visible = msoFalse '/msoTrue

[noname91]

After you've grouped a bunch of charts on the sheet, leave the group selected and see what the group's name is in the top left of the screen in the Names field (to the left of the formula bar where it normally shows the address of the active cell). You can also find the name in the Immediate pane of the VBE if, while the group is still selected, you type ?selection.name and press Enter.
Let's say it turns out to be Group 8, then you can use it in VBA thus:

  ActiveSheet.Shapes("Group 8").Visible = msoFalse '/msoTrue

Thank you for the reply p45cal!


I tried your solution and it works perfectly.



One question though. I noticed you put Visible = msoFalse instead of simply Visible = False. Is there a reason for doing so? I swapped the mso out to see what would happen, but it turns out the macro works just as well. Just curious.

[p45cal]

I noticed you put Visible = msoFalse instead of simply Visible = False. Is there a reason for doing so?

I'm sure it's the same. I just used what I saw against the .Visible property in the Locals pane of the VBE.

[Paul_Hossler]

FWIW --

The class MsoTristate has members:

msoCTrue = 1 (not supported)
msoFalse = 0

msoTristateMixed = -2 (not supported)
msoTristateToggle = -3 (not supported)
msoTristateTrue = -1


The class vbTristate has members

vbFalse = 0
vbTrue = -1
vbUseDefault = -2



Regular ol' VBA has

False = 0
True = -1

So I figured some MS Office programmer had time on his hands and decided to complicate things with the mso's , although vbUseDefault is used in some format routines

Since the enumerations translate back to a Long, it most probably won't make a difference

[noname91]

Ah I see. Thanks for confirming p45cal and Paul for providing that extra bit of info!