0

I have several OLEObjects in a spreadsheet. (They are image/pdf attachments)

When they get added I record their path in a range on a little grid. (Like a list of attachments)

I am trying to come up with a clean way to delete one of the OLEObjects. If one gets deleted, it should delete/clear the cell that is related to it.

For example:

1) I attach a pdf

2) My Macro Stores the PDF path in Cell A1 and places the OLEObject in Cell B1 (Its aligned with the top and left of B1)

If I delete the OLEObject in B1, how do I get it to clear the value of Cell A1?

Is there a way to link Range("A1") with a particular OLEObject? (So if either is deleted, then the other will be as well?)

JohnFayt
  • 107
  • 3
  • 10

1 Answers1

0

Turn on the macro recorder and select the OLE Object, then delete it. Next select the corresponding cell. Turn the macro recorder off then inspect the VBA code to get the name of the OLE Object.

You can use the ThisWorkbook change event to try to track the delete, but I think you may still have problems aligning the deleted OLE object's position because it's top may overlap the row above the one you want to delete. If you have managed to strip out the url from the OLE object, see if you can also put the name of the object in the second column next to the value you want to later delete. This may give you the tag you need to locate the same row.

rgo
  • 481
  • 4
  • 11
  • Hey rgo, the issue here is that, the change event doesn't seem to recognize deleting an OLEObject as an event. – JohnFayt Mar 30 '15 at 14:29
  • refer to the following and use the Worksheet_SelectionChange event:http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba – rgo Apr 01 '15 at 03:30
  • Also see this post using the application.onkey event. http://stackoverflow.com/questions/7981055/trap-delete-key – rgo Apr 01 '15 at 12:39