Grouping objects

[SuitedAces]

Can any tell me how I can assign a name to a group of objects
so that in code I can set properties as a group.

For example set visible to false for a number of buttons and labels at the
same time.

Also is it possible to have and object belonging to more than one group?

And can I have shapes belonging to the same group as buttons and labels?

Thank You

[RB Smissaert]

There are a number of ways to do that, but the best may be with class
modules.
There are a number of tutorials about this.
This is one:
http://j-walk.com/ss/excel/tips/tip44.htm

RBS

"SuitedAces" <[email protected]> wrote
in message news:[email protected]...
>
> Can any tell me how I can assign a name to a group of objects
> so that in code I can set properties as a group.
>
> For example set visible to false for a number of buttons and labels at
> the
> same time.
>
> Also is it possible to have and object belonging to more than one
> group?
>
> And can I have shapes belonging to the same group as buttons and
> labels?
>
> * T h a n k Y o u *
>
>
> --
> SuitedAces
> ------------------------------------------------------------------------
> SuitedAces's Profile:
> http://www.excelforum.com/member.php...o&userid=35840
> View this thread: http://www.excelforum.com/showthread...hreadid=557641
>

[Ken Johnson]

Hi SuitedAces,

Are these buttons, labels and shapes on a worksheet or on a userform?

I'm guessing worksheet.

I don't know that using names would be of any use.

When I want to control the properties of different shapes (shapes
includes a lot of objects such as AutoShapes, TextBoxes, Buttons,
Pictures, etc even the little arrows attached to filters and dropdown
lists) I code them into different New Collections.

Before I can do this I have to edit the shapes' names in the Name Box
on the left of the Formula Bar, so that the code can recognise which
shape to place in the different New Collections.

Here's a simple example...

I added a Rectangle, Oval, Label and a Button (from the Forms ToolBar)
to the worksheet.
I want to add the Rectangle and the Oval to a New Collection named ncX,
the Label and the Button to a New Collection named ncY and the Oval and
the Button to a New Collection named ncZ.(to illustrate that the same
shape can be in more than one collection)

I edited the names in the Name Box to X_Rectangle, XZOval, Y_Label and
YZButton, so that the code can group them like this...

ncX = X_Rectangle, XZOval
ncY = Y_Label and YZButton
ncZ = XZOval, YZButton

I added six Forms Buttons to the sheet so that I could easily control
the following macros that control the visible property of the shapes.
The button captions are "Show X", "Hide X", "Show Y", "Hide Y", "Show
Z" and "Hide Z".

The code is...

Public ncX As New Collection
Public ncY As New Collection
Public ncZ As New Collection
Public Shp As Shape

Public Sub GroupShapes()
For Each Shp In ActiveSheet.Shapes
Select Case Left(Shp.Name, 1)
Case "X"
ncX.Add Item:=Shp
Case "Y"
ncY.Add Item:=Shp
End Select
If Mid(Shp.Name, 2, 1) = "Z" Then
ncZ.Add Item:=Shp
End If
Next Shp
End Sub

Public Sub HideX()
GroupShapes
For Each Shp In ncX
Shp.Visible = False
Next Shp
End Sub

Public Sub HideY()
GroupShapes
For Each Shp In ncY
Shp.Visible = False
Next Shp
End Sub

Public Sub HideZ()
GroupShapes
For Each Shp In ncZ
Shp.Visible = False
Next Shp
End Sub

Public Sub ShowX()
GroupShapes
For Each Shp In ncX
Shp.Visible = True
Next Shp
End Sub

Public Sub ShowY()
GroupShapes
For Each Shp In ncY
Shp.Visible = True
Next Shp
End Sub

Public Sub ShowZ()
GroupShapes
For Each Shp In ncZ
Shp.Visible = True
Next Shp
End Sub

The Show and Hide Buttons are each assigned to the appropriate macro.
Each of the six small macros first runs the GroupShapes macro, which
places the four shapes into the three different New Collections. When
the code returns to the triggered small macro it hides or unhides the
shapes in the affected New Collection.

When considering the changes you should make to the shapes'
automatically given names you need to avoid characters that could
possibly lead to the code selecting a shape you are not intending to be
selected eg if the selection criterion is Left(Shp.Name,1) = "R" then
R_Rectangle (Edited name) and Rectangle 1 (Automatic name) both satisfy
the test, so the unwanted Rectangle 1 ends up in the New Collection
built up by the code.So, avoid using R (Rectangle), O (Oval), B
(Button) etc, depending on the shapes on your sheet.


Ken Johnson

[SuitedAces]

THANK YOU FOR BOTH THE HELP

I have a couple of questions.

Does excel see a button from the forms tool bar as part of ActiveSheet.Shapes ?

Is there a similar way to form groups for controls created with the Control Toolbox

[Ken Johnson]

SuitedAces wrote:

> Does excel see a button from the forms tool bar as part of
> ActiveSheet.Shapes ?

Yes. It also treats Command Buttons as Shapes.


> Is there a similar way to form groups for controls created with the
> Control Toolbox

Yes. You can rename the Command Buttons either in its Properties Sheet
on the Controls ToolBar OR in the Name Box on the left of the Formula
Bar.

So, you can code buttons to hide/show other buttons, be they from
either the Forms or Controls ToolBars.

Ken Johnson

[SuitedAces]

Thank You Again

[Ken Johnson]

You're welcome SuitedAces.
Thanks for the feedback.

Ken Johnson