Use shape location to clear cells

[Guest]

I would like to use a series of shapes to clear cells on a worksheet. For
example:

Shape1 exists in Cell A1. When clicked, it clears cells B1 and C1
Shape 2 exists in Cell A2. When clicked, it clears cells B2 and C2

and so on.

I need the references to be relative because I regularly re-sort the sheet
and the order of the shapes changes with all the cells. Basically, I want a
sub to figure out where the image is located and then clear the two adjacent
cells. Any ideas? Thanks!

 

[Ron de Bruin]

Hi

Try this

Dim rw As Long
rw = Range(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address).Row
Range(Cells(rw, "A"), Cells(rw, "D")).Select

 

[Guest]

I had tried to use some similar code previously, but in both cases get a Type
mismatch error. I'm using Excel 2003 if that matters. Any ideas? Thanks.

 

[Ron de Bruin]

Do you use controls from the Forms or Control toolbox

 

[Guest]

Actually neither. I was hoping to link the code via a macro to an autoshape
drawing object. If I must use controls, I would prefer to use the Control
toolbox. Thanks.

 

[Ron de Bruin]

It is working with a autoshape drawing and forms controls

Add a few autoshapes on your sheet
Assign this macro to all of them

Sub test()
Dim rw As Long
rw = Range(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address).Row
Range(Cells(rw, "A"), Cells(rw, "D")).Select
End Sub

 

[Guest]

Thanks. I was trying to run it in the code window. By assigning it to the
object, it worked perfectly! Thank you!

 

[Dave Peterson]

Or

rw = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row