how to delete the column when the shape is deleted?

[maryam]

we have two worksheets. In the first worksheet there are some shapes and in the second worksheet there are columns related to each shape.it means if we have 10 shapes in the first worksheet, there are 10 columns of data in the second worksheet. We want the relavent column to be deleted when the user delete the shape. It means if sheet1.shapes("1").delete then sheet2.columns("A:A").delete. under which event of the worksheet should I write the codes? how to write the codes?

[Bob Phillips]

You don't, a shape is not part of a worksheet, so it doesn't trigger worksheet events.

You will need to write your own code and fire it from a button say.

[maryam]

So if I write it under the click event of a button then how to say if sheets("1").shapes("1").delete is done then sheets("2").columns("A:A").delete?

[Bob Phillips]

No, you delete the selected shape from within that button click procedure, and deletre the associated columns.

[Charlize]

If you delete column B because you delete shape2. Will column C become B and when you delete shape 3 it will actually delete the previous column D and not column C because the columns shifted to the left ?

Charlize

[maryam]

Dear charlize that should not be a problem. Each column has a name in the first row, so I will look for the relavant name to delete, but I cant understand what does xld mean? I want the column to be deleted if the shape is deleted? how can i write the codes?

[maryam]

Dear xld,
I dont want to have an extra button for deleting the shapes, delete button on the keybord will do, but how to delete the column?

[Bob Phillips]

Then I think that you are up a creek so as to speak.

[geekgirlau]

What xld is talking about is that there is no event that you can use to trigger the macro. How are you planning to let the Excel know that it's time to run the macro? Shapes do not have events - you can assign a macro to run when the click the shape, but you cannot tell it to run a macro if the shape is deleted.

[maryam]

thank you geek for your reply. I dont know you, first time you reply me. what about assigning macros when shape is selected? I first select the shape and then delete it using delete button.

[Bob Phillips]

You could always refdefine the delete key and point it at your code Application.OnKey "{DELETE}", "myDelete" and then check if the selection is a shape, if so delete it and underlying columns, else just delete selection.

[maryam]

can we write sth like this:
If selection.delete then
selection.onaction=macro1
end if

[maryam]

dear xld, user may not necessarily delete the selected shape, but we want the column to the delete if the user deletes the shape. can you explain about redifining the key pls?

[maryam]

let me explain more. I have an object pallete with which i can put the shapes on the worksheet. then I have a macro to open a form for each shape when selected. the code is: selection.onaction=macro
there is another macro (macro1) for deleting the relavant columns if the selected shap is deleted with delete button. how to call macro1?

[Bob Phillips]

Read my post again, I told you exactly that.