Create a Shape collection ?

[Dude]

Hello there,

I'm not sure I'm posting in the right group, nor if my question is relevant,
but here is my problem :
I have a Shape that is represented on almost all my Sheets in an Excel file.
This shape has to be modified from times to times.

Is there a way to have those Shape items referring to a collection (or is it
a class module ?) so that I only have to modify the shape once instead of
using the oldfashioned "cut /paste" system, that is rather painfull ?
I know this is possible in an object-oriented environment, which isn't the
case in VBA, but who knows, luck happens sometimes

Thank's in advance for your help.

Dude

 

[Tushar Mehta]

Since you know that what you want to do is possible in a OOP
environment, maybe a pointer in how you are operating in a OOP
environment will get you going...

VBA access to any XL (or for that matter any Office application) object
is strictly through a top-down object hierarchy. So, the only way to
access a shape in a worksheet is in a OOP environment.

Start with the Application object and access the Workbooks collection
through the Workbooks property. Identify the workbook of interest and
access the Worksheets collection. In each worksheet of interest work
with the shape of interest.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Dude]

Thank You Tushar for this valuable information.
I could indeed reach the targeted Shapes thru this method.

My next question would be : can I somehow link each Shape to a template, and
have those Shapes modified each time I modifiy the Template ?
How do I define such a template ?

 

[Cindy M -WordMVP-]

Hi Dude,

My next question would be : can I somehow link each Shape to a template, and
have those Shapes modified each time I modifiy the Template ?
How do I define such a template ?

I think you need to pose the question a bit differently. I understand what
you're asking, and if this were Word the answer would be: link to an outside
file (graphic). Looking at the Excel interface, it appears Excel does not
support either linking in graphics, or linking to objects in other worksheets.

The only exception I can find is going over Insert/Object/From File. If I have
software installed that can act as an OLE-Server for graphics files, then I can
link to an external graphic. This isn't something I like to do, because it
isn't efficient for graphics. But I think this, or a similar approach, is all
that Excel will allow.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Dude]

Thanks Cindy for your help. I'll keep on searching for a "clean" solution,
and meanwhile work on the objects for each inserted Shape in each Worksheet.

Nevertheless, too bad that a shape depends from each perticular Sheet and
not from its parent, that is to say the entire Workbook.