VBA to Move Shapes out of Defined Range

**ChrisHook** · 05-03-2019, 11:05 AM

All,

I've developed an application that essentially takes information within a table, and generates a number of textbox call-outs based upon the information within the table. The code below moves all overlapping shapes so they're no longer overlapping (works quite well). However, i'd like to also ensure that no shapes are covering a pre-defined (and static) range (collection of cells). Let's say i'd want to make sure no shapes overlap Range("E17:H30") of the activesheet. Any ideas here?

Many thanks in advance!

Please Login or Register  to view this content.

**Andy Pope** · 05-03-2019, 11:29 AM

What happens if you create a temporary shape that covers the cell range and then gets treated the same as all the other shapes?
Does your algorithm position the callouts in a sensible way?

**ChrisHook** · 05-03-2019, 11:33 AM

Hi Andy,

Thanks for your quick reply. I've tried that and the script also moves the temporary shape out of the way of overlapping shapes, thus leaving exposed the range that i'd like to be uncovered.

**Andy Pope** · 05-03-2019, 11:55 AM

It looks like your looping will only compare shapes to the temporary one on the last iteration of the i loop. But it will move both shapes.
If you reduce the loop then it will not be tested against any shapes.

Can you post a small example and perhaps create a before and after so we can see how the shapes would be moved away from cells?

**ChrisHook** · 05-03-2019, 12:48 PM

Hi Andy,

Sure thing. I've attached an unprotected sample workbook with instructions within.

Thanks again for your quick feedback!

**Andy Pope** · 05-04-2019, 05:09 AM

Have a look at this.
I have made some changes to the way the shapes are named so the code can tell which shapes are related.
I have also named a local name to the RiskTemplate that defines the risk grid.

Once the newly named callout textbox, line and dot have been added and run your overlap check it then checks against the named range.
Depending on how messed up the callout become, when manualled moved, the clearing of grid may reintroduce callout overlap.

Have you considered using the comments to hold this information?

**ChrisHook** · 05-06-2019, 09:35 AM

Andy,

This seems to work great. I hadn't considered your method of defining the grid.

I have brought up your point regarding comments. That may be an easier route in the future.

Thanks again for your help!

VBA to Move Shapes out of Defined Range

LinkBack

Thread Tools

Rate This Thread

Display

VBA to Move Shapes out of Defined Range

Re: VBA to Move Shapes out of Defined Range

Re: VBA to Move Shapes out of Defined Range

Re: VBA to Move Shapes out of Defined Range

Re: VBA to Move Shapes out of Defined Range

Re: VBA to Move Shapes out of Defined Range

Re: VBA to Move Shapes out of Defined Range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If word entered not in defined name range, copy & paste it to bottom of defined name range

[SOLVED] insert Pre-defined text and hide shapes issue

Different Display Scales move shapes

[SOLVED] Move Two Shapes In The Same Loop

how to move excel shapes

Move Shapes to Grid in Excel

How To Move Shapes From Another Shapes? (floating shapes)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions