Greetings,
I just upgraded to Excel 2003 a few weeks ago - and the IT
department left out some VBA help files! It is going to be a couple of
days before I have time to wander over there, and in the mean time I'm
discovering that few books on Excel Programming say much about the
drawing layer. So...

I have a fairly large group of shapes grouped into a shape named
"network" , which consists of nodes, edges and costs on the edges. I
want to be able to change the cost displayed on certain edges (the
costs themselves are in text boxes). I gather from google that you have
to ungroup - change - regroup. I can ungroup and change no problem - it
is the regroup that is throwing me. Based on some code snippets I saw
on this group, I would think that the following should work, but it
throws an error:

Sub changeCost(costName As String, newCost As Long)

Dim shpRng As ShapeRange, network As Shape
Dim A As Variant
Dim i As Long, n As Long
Set network = Shapes("network")
n = network.GroupItems.Count
ReDim A(0 To n - 1)
For i = 1 To n
A(i - 1) = network.GroupItems(i).Name
Next i
network.Ungroup
Shapes(costName).TextFrame.Characters.Text = newCost

Set shpRng = Shapes.Range(A)
shpRng.Group
shpRng.Name = "network"

End Sub
+++++++++++++++++++

The line which causes problems is: Set shpRng = Shapes.Range(A)

What bozo-like programming error am I making? Any help would be
appreciated. Is there some sort of way to directly transfer the
groupitems to a shaperange?

Thanks for reading this

-semiopen

Try changing...
Set network = Shapes("network")
Set shpRng = Shapes.Range(A)
shpRng.Group

-to-
Set network = ActiveSheet.Shapes("network")
Set shpRng = ActiveSheet.Shapes.Range(A)
shpRng.ReGroup
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



wrote in message
Greetings,
I just upgraded to Excel 2003 a few weeks ago - and the IT
department left out some VBA help files! It is going to be a couple of
days before I have time to wander over there, and in the mean time I'm
discovering that few books on Excel Programming say much about the
drawing layer. So...
I have a fairly large group of shapes grouped into a shape named
"network" , which consists of nodes, edges and costs on the edges. I
want to be able to change the cost displayed on certain edges (the
costs themselves are in text boxes). I gather from google that you have
to ungroup - change - regroup. I can ungroup and change no problem - it
is the regroup that is throwing me. Based on some code snippets I saw
on this group, I would think that the following should work, but it
throws an error:

Sub changeCost(costName As String, newCost As Long)
Dim shpRng As ShapeRange, network As Shape
Dim A As Variant
Dim i As Long, n As Long
Set network = Shapes("network")
n = network.GroupItems.Count
ReDim A(0 To n - 1)
For i = 1 To n
A(i - 1) = network.GroupItems(i).Name
Next i
network.Ungroup
Shapes(costName).TextFrame.Characters.Text = newCost

Set shpRng = Shapes.Range(A)
shpRng.Group
shpRng.Name = "network"

End Sub
+++++++++++++++++++

The line which causes problems is: Set shpRng = Shapes.Range(A)
What bozo-like programming error am I making? Any help would be
appreciated. Is there some sort of way to directly transfer the
groupitems to a shaperange?
Thanks for reading this
-semiopen

Thank you for your suggestion. Unfortunately, now

Set shpRng = ActiveSheet.Shapes.Range(A)

throws the dreaded run time error 1004

-semiopen

Jim Cone wrote:
Try changing...
Set network = Shapes("network")
Set shpRng = Shapes.Range(A)
shpRng.Group

-to-
Set network = ActiveSheet.Shapes("network")
Set shpRng = ActiveSheet.Shapes.Range(A)
shpRng.ReGroup
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



wrote in message
Greetings,
I just upgraded to Excel 2003 a few weeks ago - and the IT
department left out some VBA help files! It is going to be a couple of
days before I have time to wander over there, and in the mean time I'm
discovering that few books on Excel Programming say much about the
drawing layer. So...
I have a fairly large group of shapes grouped into a shape named
"network" , which consists of nodes, edges and costs on the edges. I
want to be able to change the cost displayed on certain edges (the
costs themselves are in text boxes). I gather from google that you have
to ungroup - change - regroup. I can ungroup and change no problem - it
is the regroup that is throwing me. Based on some code snippets I saw
on this group, I would think that the following should work, but it
throws an error:

Sub changeCost(costName As String, newCost As Long)
Dim shpRng As ShapeRange, network As Shape
Dim A As Variant
Dim i As Long, n As Long
Set network = Shapes("network")
n = network.GroupItems.Count
ReDim A(0 To n - 1)
For i = 1 To n
A(i - 1) = network.GroupItems(i).Name
Next i
network.Ungroup
Shapes(costName).TextFrame.Characters.Text = newCost

Set shpRng = Shapes.Range(A)
shpRng.Group
shpRng.Name = "network"

End Sub
+++++++++++++++++++

The line which causes problems is: Set shpRng = Shapes.Range(A)
What bozo-like programming error am I making? Any help would be
appreciated. Is there some sort of way to directly transfer the
groupitems to a shaperange?
Thanks for reading this
-semiopen

Are you sure that network is still a group?
Did you add "ActiveSheet." in front of ...
Shapes(costName).TextFrame.Characters.Text = newCost

Jim Cone
http://www.officeletter.com/blink/specialsort.html



wrote in message
Thank you for your suggestion. Unfortunately, now
Set shpRng = ActiveSheet.Shapes.Range(A)

throws the dreaded run time error 1004
-semiopen

Jim Cone wrote:
Try changing...
Set network = Shapes("network")
Set shpRng = Shapes.Range(A)
shpRng.Group

-to-
Set network = ActiveSheet.Shapes("network")
Set shpRng = ActiveSheet.Shapes.Range(A)
shpRng.ReGroup
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Jim Cone wrote:
Are you sure that network is still a group?
Did you add "ActiveSheet." in front of ...
Shapes(costName).TextFrame.Characters.Text = newCost

I didn't add the activesheet to *that* line - but that doesn't matter
since it is an implied parent object. After my sub dies - when I look
at Sheet1 (where this resides) - I see a whole bunch of ungrouped
shapes *with the cost changed to reflect the new cost*. When I step
through a test run with F8 - it is definitely the line I indicated that
Excel is balking at. Wierd.

Jim Cone
http://www.officeletter.com/blink/specialsort.html



wrote in message
Thank you for your suggestion. Unfortunately, now
Set shpRng = ActiveSheet.Shapes.Range(A)

throws the dreaded run time error 1004
-semiopen

Jim Cone wrote:
Try changing...
Set network = Shapes("network")
Set shpRng = Shapes.Range(A)
shpRng.Group

-to-
Set network = ActiveSheet.Shapes("network")
Set shpRng = ActiveSheet.Shapes.Range(A)
shpRng.ReGroup
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

I have never heard of an "implied parent object", but if Excel is
accepting "Shapes" without specifying the sheet that it belongs
to, I certainly want to learn more.

To fix your problem, make the variant "A" a real array...
Dim A() as Variant.

Jim Cone
San Francisco, USA

wrote in message oups.com...

Jim Cone wrote:
Are you sure that network is still a group?
Did you add "ActiveSheet." in front of ...
Shapes(costName).TextFrame.Characters.Text = newCost

I didn't add the activesheet to *that* line - but that doesn't matter
since it is an implied parent object. After my sub dies - when I look
at Sheet1 (where this resides) - I see a whole bunch of ungrouped
shapes *with the cost changed to reflect the new cost*. When I step
through a test run with F8 - it is definitely the line I indicated that
Excel is balking at. Wierd.

Jim Cone
http://www.officeletter.com/blink/specialsort.html



wrote in message
Thank you for your suggestion. Unfortunately, now
Set shpRng = ActiveSheet.Shapes.Range(A)

throws the dreaded run time error 1004
-semiopen

Jim Cone wrote:
Try changing...
Set network = Shapes("network")
Set shpRng = Shapes.Range(A)
shpRng.Group

-to-
Set network = ActiveSheet.Shapes("network")
Set shpRng = ActiveSheet.Shapes.Range(A)
shpRng.ReGroup
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Well,

My original post was bone-headed in two ways:

1) My help files were there after all - just compressed or something
and I didn't need the CD to intall them :) This allowed me to realize
that ...

2) I should have been focusing on the Regroup method:

Sub changeCost(costName As String, newCost As Long)

Dim network As Shape
Dim i As Long, n As Long
Set network = ActiveSheet.Shapes("network")
network.Ungroup
ActiveSheet.Shapes(costName).TextFrame.Characters. Text = newCost
Set network = ActiveSheet.Shapes.Range(costName).Regroup
network.Name = "network"

End Sub

which works fine

The Regroup method saves me from the hassle of keeping track of the
original shapes.
Still, I'm sure that I'll sooner or later use the ( ) device to force
an evaluation that Nick and Peter discussed, so I'm glad I did it the
hard way first.

-semiopen

wrote:
Greetings,
I just upgraded to Excel 2003 a few weeks ago - and the IT
department left out some VBA help files! It is going to be a couple of
days before I have time to wander over there, and in the mean time I'm
discovering that few books on Excel Programming say much about the
drawing layer. So...

I have a fairly large group of shapes grouped into a shape named
"network" , which consists of nodes, edges and costs on the edges. I
want to be able to change the cost displayed on certain edges (the
costs themselves are in text boxes). I gather from google that you have
to ungroup - change - regroup. I can ungroup and change no problem - it
is the regroup that is throwing me. Based on some code snippets I saw
on this group, I would think that the following should work, but it
throws an error:

Sub changeCost(costName As String, newCost As Long)

Dim shpRng As ShapeRange, network As Shape
Dim A As Variant
Dim i As Long, n As Long
Set network = Shapes("network")
n = network.GroupItems.Count
ReDim A(0 To n - 1)
For i = 1 To n
A(i - 1) = network.GroupItems(i).Name
Next i
network.Ungroup
Shapes(costName).TextFrame.Characters.Text = newCost

Set shpRng = Shapes.Range(A)
shpRng.Group
shpRng.Name = "network"

End Sub
+++++++++++++++++++

The line which causes problems is: Set shpRng = Shapes.Range(A)

What bozo-like programming error am I making? Any help would be
appreciated. Is there some sort of way to directly transfer the
groupitems to a shaperange?

Thanks for reading this

-semiopen

Well, yes, there that easy way of doing it....
Not sure how applicable all that variant and brackets stuff was, but at
least you are aware that things may not be as they seem sometimes.

NickHK

wrote in message
oups.com...
Well,

My original post was bone-headed in two ways:

1) My help files were there after all - just compressed or something
and I didn't need the CD to intall them :) This allowed me to realize
that ...

2) I should have been focusing on the Regroup method:

Sub changeCost(costName As String, newCost As Long)

Dim network As Shape
Dim i As Long, n As Long
Set network = ActiveSheet.Shapes("network")
network.Ungroup
ActiveSheet.Shapes(costName).TextFrame.Characters. Text = newCost
Set network = ActiveSheet.Shapes.Range(costName).Regroup
network.Name = "network"

End Sub

which works fine

The Regroup method saves me from the hassle of keeping track of the
original shapes.
Still, I'm sure that I'll sooner or later use the ( ) device to force
an evaluation that Nick and Peter discussed, so I'm glad I did it the
hard way first.

-semiopen

wrote:
Greetings,
I just upgraded to Excel 2003 a few weeks ago - and the IT
department left out some VBA help files! It is going to be a couple of
days before I have time to wander over there, and in the mean time I'm
discovering that few books on Excel Programming say much about the
drawing layer. So...

I have a fairly large group of shapes grouped into a shape named
"network" , which consists of nodes, edges and costs on the edges. I
want to be able to change the cost displayed on certain edges (the
costs themselves are in text boxes). I gather from google that you have
to ungroup - change - regroup. I can ungroup and change no problem - it
is the regroup that is throwing me. Based on some code snippets I saw
on this group, I would think that the following should work, but it
throws an error:

Sub changeCost(costName As String, newCost As Long)

Dim shpRng As ShapeRange, network As Shape
Dim A As Variant
Dim i As Long, n As Long
Set network = Shapes("network")
n = network.GroupItems.Count
ReDim A(0 To n - 1)
For i = 1 To n
A(i - 1) = network.GroupItems(i).Name
Next i
network.Ungroup
Shapes(costName).TextFrame.Characters.Text = newCost

Set shpRng = Shapes.Range(A)
shpRng.Group
shpRng.Name = "network"

End Sub
+++++++++++++++++++

The line which causes problems is: Set shpRng = Shapes.Range(A)

What bozo-like programming error am I making? Any help would be
appreciated. Is there some sort of way to directly transfer the
groupitems to a shaperange?

Thanks for reading this

-semiopen