Mach1pro
asked on
How can I trap a Row(s) delete in Excel
I have an Excel sheet that uses data from an Access database. I would like to use VBA to ask the user if they would like the data deleted from the database and then delete the corresponding record in Access. I don't have any problem writing the sql to delete the record, but unlike an Access form there is NoBefore Delete events. Is there a way I can trap the delete/Cut event of a row(s)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well the obvious thing that sprung to mind is to unlock all the cells needed and protect the worksheet. Then procide the user with a delete button, command button, or menu option to press that takes the current highlighted row, asks "are you sure", flags the access record for deletion, unprotects the sheet and removes the row and reprotects.
As long as you make sure there is at least one cell locked on each row then the user can't highlight the whole row and click Delete to remove or even clear the contents. They can of course change/clear cells that are unlocked but otherwise it would be a read only sheet....
sheets("sheet1").unprotect
existing delete code...
sheets("sheet1").protect "mypw"
Steve
As long as you make sure there is at least one cell locked on each row then the user can't highlight the whole row and click Delete to remove or even clear the contents. They can of course change/clear cells that are unlocked but otherwise it would be a read only sheet....
sheets("sheet1").unprotect
existing delete code...
sheets("sheet1").protect "mypw"
Steve
ASKER
I've decided to use a variation of your answer.
Instead of trying to trap deleted rows, I put the value of the database table recordid into an array when the page is activated. There is already a button on the worksheet that the user clicks to complete the calculations and update the database. At this point I can loop through the values in my array and if that value isn't found anywhere in the corresponding column of the worksheet, then I know that it is supposed to be deleted from the database. Although not implemented, I could alert the user before deleting the data from the database on each missing RecordID and give the option to delete it or leave it.
Instead of trying to trap deleted rows, I put the value of the database table recordid into an array when the page is activated. There is already a button on the worksheet that the user clicks to complete the calculations and update the database. At this point I can loop through the values in my array and if that value isn't found anywhere in the corresponding column of the worksheet, then I know that it is supposed to be deleted from the database. Although not implemented, I could alert the user before deleting the data from the database on each missing RecordID and give the option to delete it or leave it.
When you delete a row the worksheet_SelectionChange event is triggered when selecting the row (which doesn't mean you're going to delete it right) and the worksheet_Change event is triggered after the deletion (since the next rows will all change value). However since the event is triggered AFTER the deletion you don't even know what was the address of the deleted row, let alone it's contents.
Even application events won't cover this.
Maybe not what you wanted to hear, but I think that there's no other way.
Anyway, wait a couple of days... maybe someone else can provide a workaround for this.
PAulo