Worksheet KeyPress event

[bujubenji]

Hi all,

This might be a basic one, but is it possible to trigger a vba event if cells on a worksheet are cleared? To provide a little context, a have a userform that will drop data into columns B:E starting on row 44. The next entry would be row 45 and so on. I have a macro in place on a shape acting as a button, which will copy that data over to the next clear row in columns H:K when the product has been sent to Quality Assurance. If that button is pressed again, the data that was sent over will be cleared and subsequent data will be shifted up.

However, I also want to make it so that if data is cleared from a row in columns H:K by pressing delete, subsequent data will move up into the now cleared cells.

The shift up component is fine and works as part of the aforementioned macro that clears cells: (Range("H44:K58").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp)

Would an easier solution just be protecting the cells with something like the following?:

"Dim Sh As Worksheet
For Each Sh In Worksheets
Sh.Protect userinterfaceonly:=True
Next

End Sub"

Many thanks,

Ben

 

[bobsan42]

if you want reliable data, better make sure data can be manipulated in a controlled fashion - protect the sheets.
other people actions are too unpredictable.