EXCEL VBA help

kompiuteriumeistras · July 2nd, 2012, 02:53 AM

Hi,
there is the EXCEL VBA code:

Code:

 Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 5 Then
 EIL_SKAICIUS = 0
 ESAMA_EILUTE = ActiveCell.Row
 EIL_SKAICIUS = Application.CountA(Range("A:A"))
 Sheets("SARASAS").Cells(EIL_SKAICIUS + 1, 2).Value = Sheets("SARASAS").Cells(ESAMA_EILUTE - 1, 5).Value
 Sheets("SARASAS").Cells(EIL_SKAICIUS + 1, 3).Value = Sheets("SARASAS").Cells(ESAMA_EILUTE - 1, 3).Value
 Sheets("SARASAS").Cells(EIL_SKAICIUS + 1, 4).Value = Sheets("SARASAS").Cells(ESAMA_EILUTE - 1, 4).Value
 End If
 End Sub

Now, after when in 5th column write a value, system automaticaly copy 3,4,5 columns values of actual row to new row 2,3,4 columns of end list. I need, what after press DELETE buton, code would be not activ, do nothink, but DELETE key function would be working (delete cell value). How to do it ?

Apostolos55 · July 11th, 2012, 05:46 AM

does this simple idea help you?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Empty Then
        Debug.Print "probably pressed delete"
    Else
        Debug.Print "New value is:"; Target.Value
    End If
End Sub

if yes then you can probably change

Code:

If Target.Column = 5 Then

to

Code:

If Target.Column = 5 and target<>empty Then

it doesn't trap or affect the key-press but deals with the effect