Grouping objects
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 |
Grouping objects
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" wrote in message ... 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 |
Grouping objects
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 |
Grouping objects
THANK YOU FOR BOTH THE HEL I have a couple of questions. Does excel see a button from the forms tool bar as part o ActiveSheet.Shapes ? Is there a similar way to form groups for controls created with th Control Toolbo -- SuitedAce ----------------------------------------------------------------------- SuitedAces's Profile: http://www.excelforum.com/member.php...fo&userid=3584 View this thread: http://www.excelforum.com/showthread.php?threadid=55764 |
Grouping objects
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 |
Grouping objects
* T h a n k Y o u A g a i n -- SuitedAce ----------------------------------------------------------------------- SuitedAces's Profile: http://www.excelforum.com/member.php...fo&userid=3584 View this thread: http://www.excelforum.com/showthread.php?threadid=55764 |
Grouping objects
You're welcome SuitedAces. Thanks for the feedback. Ken Johnson |
All times are GMT +1. The time now is 03:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com