Can I select and Group Autoshapes in Excel 97 ?

[diglas1 via OfficeKB.com]

I create about 200 rectangles on a spreadsheet, then find that to delete them
in the user interface, I've got to manually select each...can't seem to
select them all at once.
I found some answers in FAQ for a VBA solution, but can't seem to get it to
work...perhaps a limitation of Excel 97 ?

Dim WSO As Worksheet
Set WSO = ActiveSheet
WSO.Shapes.Select

Error: "Object doesn't support this property or method"

Does this mean that Excel doesn't support this, or have I missed something ?

However, the following more labour intensive method does work, but I can't
predict how many rectangles I've to select and I'm unable to work out a loop
to populate the "Rectangle 1","Rectangle 2" etc list...can't seem to be able
to create a variable that holds "Rectangle 1","Rectangle 2" without
enclosing the lot in quotes("") as it sees it as a string. Even if I could,
can't get the Array statement to accept a variable as argument list instead
of hardcoded text.

ActiveSheet.Shapes.Range(Array("Rectangle 1":"Rectangle 2")).Select

Any suggestions welcome
Thanks in anticipation.

 

[David McRitchie]

see Shapes
http://www.mvps.org/dmcritchie/excel/shapes.htm

 

[Don Guillett]

try
for each sh in shapes
sh.delete
next sh
or
for each sh in sheets("hasshapes").shapes
sh.delete
next sh

 

[diglas1 via OfficeKB.com]

David , I'm just off to bed and saw your response, so many thanks, haven't
read your site yet but looks fantastic...will dive in tomorrow.

see Shapes
http://www.mvps.org/dmcritchie/excel/shapes.htm

I create about 200 rectangles on a spreadsheet, then find that to delete them
in the user interface, I've got to manually select each...can't seem to

[quoted text clipped - 22 lines]

Any suggestions welcome
Thanks in anticipation.

 

[diglas1 via OfficeKB.com]

Don, thanks for that, it works a treat, but what I'd prefer to do is to
select the lot so I can delete, or save the group elsewhere.

David , I'm just off to bed and saw your response, so many thanks, haven't
read your site yet but looks fantastic...will dive in tomorrow.

see Shapes
http://www.mvps.org/dmcritchie/excel/shapes.htm

[quoted text clipped - 9 lines]

 

[Don Guillett]

You can hold downt the control key>select each>copy or cut

--
Don Guillett
SalesAid Software
(e-mail address removed)

Don, thanks for that, it works a treat, but what I'd prefer to do is to
select the lot so I can delete, or save the group elsewhere.

David , I'm just off to bed and saw your response, so many thanks, haven't
read your site yet but looks fantastic...will dive in tomorrow.

see Shapes
http://www.mvps.org/dmcritchie/excel/shapes.htm

[quoted text clipped - 9 lines]

Any suggestions welcome
Thanks in anticipation.

 

[Debra Dalgleish]

To select all the objects on the sheet --
Choose Edit>Go To, click Special
Select Objects, click OK

Then, press the Delete key, to delete the selected objects.

Or, to work with specific objects, you can add the 'Select Multiple
Objects' tool to one of your toolbars:

Choose Tools>Customize
Select the Commands tab
Select the Drawing Category
Drag the 'Select Multiple Objects' tool onto one of your toolbars
Click Close

Then, click the Select Multiple Objects button, and select any or all
objects on the worksheet.
Press the Delete key, to delete the selected objects.

Don, thanks for that, it works a treat, but what I'd prefer to do is to
select the lot so I can delete, or save the group elsewhere.

David , I'm just off to bed and saw your response, so many thanks, haven't
read your site yet but looks fantastic...will dive in tomorrow.

see Shapes
http://www.mvps.org/dmcritchie/excel/shapes.htm

[quoted text clipped - 9 lines]

Any suggestions welcome
Thanks in anticipation.

 

[David McRitchie]

Hi Diglas (and Debra),
Nice to know about the multiple objects.

But I think Diglas is not seeing the threading properly, and
so I'm not sure if he was actually replying to Don or to me.

But from the answers I get the impression he did not find
what I was looking at when I referred him to my shapes page
so I will provide a more specific example below and to my
htm page and to the code page.


Sub delAllRectangularShapesOnSht()
Dim shp As Shape
For Each shp In ActiveWorkbook.ActiveSheet.Shapes
'check shape code for Particular Shapes:
If shp.AutoShapeType = msoShapeRectangle Then shp.Delete
Next shp
End Sub

Right at the top of the Shapes page:
http://www.mvps.org/dmcritchie/excel/shapes.htm
is a reference to the code in
http://www.mvps.org/dmcritchie/excel/code/shapes.txt

within the shapes.txt file are two subroutines one will remove all
rectangular shapes that have their upper left corner in the
cell selection area. I've added the example above to both
the shapes.htm page and the code/shapes.txt file.

One of the reasons for having a text version of the code is to
be able to have more complete subroutines, a few additional
subroutines, and to make sure that HTML is not going to interfere
with copying code. Especially for such things as greater than,
and less than signs, and the use of ampersands.

To select all the objects on the sheet --
Choose Edit>Go To, click Special
Select Objects, click OK

Then, press the Delete key, to delete the selected objects.

Or, to work with specific objects, you can add the 'Select Multiple
Objects' tool to one of your toolbars:

Choose Tools>Customize
Select the Commands tab
Select the Drawing Category
Drag the 'Select Multiple Objects' tool onto one of your toolbars
Click Close

Then, click the Select Multiple Objects button, and select any or all
objects on the worksheet.
Press the Delete key, to delete the selected objects.

Don, thanks for that, it works a treat, but what I'd prefer to do is to
select the lot so I can delete, or save the group elsewhere.

David , I'm just off to bed and saw your response, so many thanks, haven't
read your site yet but looks fantastic...will dive in tomorrow.


see Shapes
http://www.mvps.org/dmcritchie/excel/shapes.htm

[quoted text clipped - 9 lines]

Any suggestions welcome
Thanks in anticipation.

 

[diglas1 via OfficeKB.com]

Debra,
This is great, it actually solves the problem, would never have found it
myself. Thanks a lot.

To select all the objects on the sheet --
Choose Edit>Go To, click Special
Select Objects, click OK

Then, press the Delete key, to delete the selected objects.

Or, to work with specific objects, you can add the 'Select Multiple
Objects' tool to one of your toolbars:

Choose Tools>Customize
Select the Commands tab
Select the Drawing Category
Drag the 'Select Multiple Objects' tool onto one of your toolbars
Click Close

Then, click the Select Multiple Objects button, and select any or all
objects on the worksheet.
Press the Delete key, to delete the selected objects.

Don, thanks for that, it works a treat, but what I'd prefer to do is to
select the lot so I can delete, or save the group elsewhere.

[quoted text clipped - 9 lines]

 

[diglas1 via OfficeKB.com]

Don,
Thanks for your suggestion, but the problem was that I had hundreds of tiny
shapes which were very difficult to select so didn't want to manually select
each...however, Debra's answer showing how to select the lot in the user
interface solves this for me.
Thanks again.

You can hold downt the control key>select each>copy or cut

Don, thanks for that, it works a treat, but what I'd prefer to do is to
select the lot so I can delete, or save the group elsewhere.

[quoted text clipped - 7 lines]

 

[diglas1 via OfficeKB.com]

David(,Don and Debra),
Apologies if my replies have been to the wrong threads, but the names
addressed to each are correct. This is my first forray into this site, in
fact any help site and I now see how the threads stretch down the LHS....
however, I think I replied to the correct response buttons.
Anyway, thank you all for your kind suggestions, I trawled the net for days
and tries various books to no avail, unable to find answers...I've gratefully
learned a great deal.

David specifically, thanks, I see that your excellent Shapes page also had
Debra's answer.

For the sake of future browsers of these answers, I was still unable to get
the shapes to "Group" in VBA, which was my original problem, but having
found the user interface answer from D&D, just ran it as a macro to get the
code, which is:

ActiveSheet.DrawingObjects.Select
Selection.ShapeRange.Group.Select

Thanks all of you again.

Hi Diglas (and Debra),
Nice to know about the multiple objects.

But I think Diglas is not seeing the threading properly, and
so I'm not sure if he was actually replying to Don or to me.

But from the answers I get the impression he did not find
what I was looking at when I referred him to my shapes page
so I will provide a more specific example below and to my
htm page and to the code page.

Sub delAllRectangularShapesOnSht()
Dim shp As Shape
For Each shp In ActiveWorkbook.ActiveSheet.Shapes
'check shape code for Particular Shapes:
If shp.AutoShapeType = msoShapeRectangle Then shp.Delete
Next shp
End Sub

Right at the top of the Shapes page:
http://www.mvps.org/dmcritchie/excel/shapes.htm
is a reference to the code in
http://www.mvps.org/dmcritchie/excel/code/shapes.txt

within the shapes.txt file are two subroutines one will remove all
rectangular shapes that have their upper left corner in the
cell selection area. I've added the example above to both
the shapes.htm page and the code/shapes.txt file.

One of the reasons for having a text version of the code is to
be able to have more complete subroutines, a few additional
subroutines, and to make sure that HTML is not going to interfere
with copying code. Especially for such things as greater than,
and less than signs, and the use of ampersands.

To select all the objects on the sheet --
Choose Edit>Go To, click Special

[quoted text clipped - 28 lines]

 

[David McRitchie]

You are definitely in the right thread, it's just not clear if the
threading is correct, replies to a reply within the thread. It looks
more like the reply is to the last post within a thread rather than
to a specific post.

I don't think Debra's answer was anywhere on my page, but it will
be by the time this is posted.

I like to do web searches and many of the web based newsreaders
put newsgroup postings into web pages, and then get picked up by
Google web search which is bad. Accessing newsgroups without HTML (directly) is best, Google Groups even though HTML does not
mess up posting structure within a thread, but they do try to portray ownership of the usenet news groups like the bad web
"portals".

 

[diglas1 via OfficeKB.com]

David,
See your Shapes page for the main gist of Debra's answer:
You can manually select all objects on a sheet regardless of what cells are
selected with Edit, GoTo (Ctrl+G), Special, Objects.

By the way, do you know if this posting is now considered "closed", since it
answered my initial query ?
i.e. if I had something else to ask of the correspondents, would they be
alerted if they didn't deliberaltely look back here...or is it best to start
a new query ?
Thanks again

You are definitely in the right thread, it's just not clear if the
threading is correct, replies to a reply within the thread. It looks
more like the reply is to the last post within a thread rather than
to a specific post.

I don't think Debra's answer was anywhere on my page, but it will
be by the time this is posted.

I like to do web searches and many of the web based newsreaders
put newsgroup postings into web pages, and then get picked up by
Google web search which is bad. Accessing newsgroups without HTML (directly) is best, Google Groups even though HTML does not
mess up posting structure within a thread, but they do try to portray ownership of the usenet news groups like the bad web
"portals".

Apologies if my replies have been to the wrong threads, but the names
addressed to each are correct.

[quoted text clipped - 51 lines]

 

[David McRitchie]

Yes, but that selects all shapes, and doesn't give you the
opportunity to delete all rectangles as once. I guess I was
looking more at Debra's reply to select multiple objects which gives you
a checklist of objects to select, which allows you to select
all rectangles then delete them as a selection.

OfficeKB might have an indication that a posting is closed, or open,
or that someone's answer was good or bad. Such bells and whistles
are not in Usenet and are strictly to try to steal the usenet so they can
force feed advertisements. Other such grubby sites include excelforum(s), exceltip and about 80 other revenue hungry sites.
Most of us see only what
is posted as plain text.

it is basically up to you -- it is your thread, but if the second question
doesn't fit the subject then it would be much better to start
another thread so that your question would be more likely to help
others as well. Sometimes if someone searches on something
they think they need, they also find the something they need along
with it when there is more than one question. Generally though it
works best to only have one question -- people can see the questions
were answered and not think part of a question already answered was
forgotten due to a digression.

To me the overriding thing is the use of Google Groups to search
for newsgroup postings. I would suggest posting directly to newsgroups
though rather than through a web portal. Google Groups also operates
as a portal, but at least does copy newsgroups onto web page to attract web page search engines as does officekb, excelforum,
exceltips and at least 80 other such crappy sites that destroy things for everyone.

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

David,
See your Shapes page for the main gist of Debra's answer:
You can manually select all objects on a sheet regardless of what cells are
selected with Edit, GoTo (Ctrl+G), Special, Objects.

By the way, do you know if this posting is now considered "closed", since it
answered my initial query ?
i.e. if I had something else to ask of the correspondents, would they be
alerted if they didn't deliberaltely look back here...or is it best to start
a new query ?
Thanks again

You are definitely in the right thread, it's just not clear if the
threading is correct, replies to a reply within the thread. It looks
more like the reply is to the last post within a thread rather than
to a specific post.

I don't think Debra's answer was anywhere on my page, but it will
be by the time this is posted.

I like to do web searches and many of the web based newsreaders
put newsgroup postings into web pages, and then get picked up by
Google web search which is bad. Accessing newsgroups without HTML (directly) is best, Google Groups even though HTML does not
mess up posting structure within a thread, but they do try to portray ownership of the usenet news groups like the bad web
"portals".

Apologies if my replies have been to the wrong threads, but the names
addressed to each are correct.

[quoted text clipped - 51 lines]

My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Don Guillett]

And Ron DeBruins google addin is good
http://www.rondebruin.nl/

--
Don Guillett
SalesAid Software
(e-mail address removed)

Yes, but that selects all shapes, and doesn't give you the
opportunity to delete all rectangles as once. I guess I was
looking more at Debra's reply to select multiple objects which gives you
a checklist of objects to select, which allows you to select
all rectangles then delete them as a selection.

OfficeKB might have an indication that a posting is closed, or open,
or that someone's answer was good or bad. Such bells and whistles
are not in Usenet and are strictly to try to steal the usenet so they can
force feed advertisements. Other such grubby sites include excelforum(s),
exceltip and about 80 other revenue hungry sites.
Most of us see only what
is posted as plain text.

it is basically up to you -- it is your thread, but if the second question
doesn't fit the subject then it would be much better to start
another thread so that your question would be more likely to help
others as well. Sometimes if someone searches on something
they think they need, they also find the something they need along
with it when there is more than one question. Generally though it
works best to only have one question -- people can see the questions
were answered and not think part of a question already answered was
forgotten due to a digression.

To me the overriding thing is the use of Google Groups to search
for newsgroup postings. I would suggest posting directly to newsgroups
though rather than through a web portal. Google Groups also operates
as a portal, but at least does copy newsgroups onto web page to attract
web page search engines as does officekb, excelforum,
exceltips and at least 80 other such crappy sites that destroy things for
everyone.

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

David,
See your Shapes page for the main gist of Debra's answer:
You can manually select all objects on a sheet regardless of what cells
are
selected with Edit, GoTo (Ctrl+G), Special, Objects.

By the way, do you know if this posting is now considered "closed", since
it
answered my initial query ?
i.e. if I had something else to ask of the correspondents, would they be
alerted if they didn't deliberaltely look back here...or is it best to
start
a new query ?
Thanks again

You are definitely in the right thread, it's just not clear if the
threading is correct, replies to a reply within the thread. It looks
more like the reply is to the last post within a thread rather than
to a specific post.

I don't think Debra's answer was anywhere on my page, but it will
be by the time this is posted.

I like to do web searches and many of the web based newsreaders
put newsgroup postings into web pages, and then get picked up by
Google web search which is bad. Accessing newsgroups without HTML
(directly) is best, Google Groups even though HTML does not
mess up posting structure within a thread, but they do try to portray
ownership of the usenet news groups like the bad web
"portals".



Apologies if my replies have been to the wrong threads, but the names
addressed to each are correct.
[quoted text clipped - 51 lines]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

Unfortunately picking out select groups of web sites to search is all that
most people can really be bothered with, such a shame, considering
all the work that many people put into real web sites.

I guess we've pretty much ruined the thread now to ask another (related)
Excel question, sorry about that.

 

[diglas1 via OfficeKB.com]

Don and David,
Thanks both for the information about portals and usenet groups. I wasn't
and still am not aware of how each gather and handle information from the web,
but was interested to see that my main information sources, garnered from web
pages from Google searches may not be the best or most friendly to those who
put time and effort into building websites. Will now take a look into the
world on Google Groups.
Thanks again.

Unfortunately picking out select groups of web sites to search is all that
most people can really be bothered with, such a shame, considering
all the work that many people put into real web sites.

I guess we've pretty much ruined the thread now to ask another (related)
Excel question, sorry about that.

And Ron DeBruins google addin is good
http://www.rondebruin.nl/

[quoted text clipped - 6 lines]

 

[David McRitchie]

This was getting much too specific about working with newsgroups
for the current subject, so have started a separate thread in this newsgroup: see

Getting valid web searches and avoiding sites that contaminate web searches
http://groups.google.com/[email protected]