Identify shape in cell

M

m

I have a sheet that was populated with data from a table copied from a
website. One of the rows has cells which contain shapes rather than
text. I need to convert these shapes to text and delete the shapes.
Two questions:

How can I take the current selection and determine just what shape is
in it?
How can I delete that shape after I set the value property to the
desired text?

Something like TypeName(Selection) will tell me that I have a shape if
what is selected is the shape itself. I actually want to select the
cell (or a range of cells) and, based on this, figure out what the
shape might be.

Thanks!

-Martin
 
J

Joel

This is not a simple problem. First shapes are not in cells but onto of
cells. You can get each shape with the following:

For Each shp In ActiveSheet.Shapes
Shapetext = ActiveSheet.OLEObjects(shp.Name).Object
For RowCount = 1 To Rows.Count
If Range("A" & RowCount).Top > shp.Top Then
ShapeRow = RowCount
Exit For
End If
Next RowCount
For ColCount = 1 To Columns.Count
If Cells(1, ColCount).Left > shp.Left Then
ShapeCol = ColCount
Exit For
End If
Next ColCount
Cells(ShapeRow, ShapeCol) = Shapetext
shp.Delete
Next shp

The problem then becomes which cell is closest to these shapes. Cells have
both Rows and columns as well as there pixel locations found with TOP & LEFT.
The pixel location change when you make columns and rows wider or narrower.
The shapes only have Top and Bottom.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top