Rodby wrote on 03/21/2010 16:44 ET :
I tried you suggestion, but I got an error message: The object does not
support the property or method.

"Hakyab" wrote:

I believe s.parent returns the worksheet, probably s.parent.parent is the
workbook.

You want to use s.parentgroup to get the entie shape group.

I posted a problem with shape groups earlier, which did not get any
attention. There may be a bug with this object.

Best,

"Rodby" wrote:

I am trying to teach myself VBA using Step by Step by Reed Jacobson.
There is a chapter on graphical objects, and in one macro he shows how to
set up an "active map" forma generic state map
Here is the macro:
Sub MakeMapButtons()
Dim s As Shape
myNumber = 3
myName = "oregon"
myCaption = "OR"
myColor = 9
Set s = ActiveSheet.Shapes(1).GroupItems(myNumber)
's.Select
s.Name = myName
s.Fill.ForeColor.ObjectThemeColor = myColor
s.ThreeD.BevelTopDepth = 6
s.TextFrame2.TextRange = myCaption
s.TextFrame2.HorizontalAnchor = msoAnchorCenter
s.TextFrame2.VerticalAnchor = msoAnchorMiddle
s.TextFrame2.TextRange.Font.Size = 20
s.TextFrame2.TextRange.Font.Bold = msoTrue
s.TextFrame2.TextRange.Font.Fill.ForeColor.ObjectT hemeColor =
msoThemeColorLight1
s.Fill.OneColorGradient msoGradientHorizontal, 1, 0
s.TextFrame2.TextRange.Font.Reflection.Type = msoReflectionType5
s.Parent.Parent.Ungroup
s.OnAction = ThisWorkbook.Name & "!StateButton"
s.DrawingObject.ShapeRange.Regroup

Everything works fine until the third line from the bottom:
s.Parent.Parent.Ungroup

I get an error message saying that the object does not support this
property.
I copied this program from his answer file and pasted it directly into
the
macro shell, so I know that I did not make a typing error, but it still
hangs
up.

Any thoughts?

Here is a working version of this macro:

Sub MakeMapButtons()
Dim s As Shape
Dim t As Shape
myNumber = 4
myName = "Washington"
myCaption = "WS"
myColor = 6
Set t = ActiveSheet.Shapes(1)
Set s = t.GroupItems(myNumber)
s.Name = myName
s.Select
s.Fill.ForeColor.ObjectThemeColor = myColor
s.ThreeD.BevelTopDepth = 6
s.TextFrame2.TextRange = myCaption
s.TextFrame2.HorizontalAnchor = msoAnchorCenter
s.TextFrame2.VerticalAnchor = msoAnchorMiddle
s.TextFrame2.TextRange.Font.Size = 20
s.TextFrame2.TextRange.Font.Bold = msoTrue
s.TextFrame2.TextRange.Font.Fill.ForeColor.ObjectT hemeColor =
msoThemeColorLight1
s.Fill.OneColorGradient msoGradientHorizontal, 1, 0
s.TextFrame2.TextRange.Font.Reflection.Type = msoReflectionType5
MsgBox s.Name
t.Ungroup
s.OnAction = "StateButton"
s.DrawingObject.ShapeRange.Regroup
End Sub


good luck with it!!