Solved: Change event

[Digita]

Hi everyone,

When I enter a value to a cell the following code is fired up to copy that value to the next cell on the right on same column.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target(1, 2) = Target
Application.EnableEvents = True
End Sub

However, if I copy a value to a range (consisting more than 1 cell) or when I enter a value to a range (> 1 cell) by ctrl + alt + enter. This code is not activated. Any idea why?

Thanks in advance for your input.

Regards


KP

[Paul_Hossler]

Try this

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

With Target

.Offset(0, .Columns.Count).Resize(.Rows.Count, .Columns.Count).Value = .Value

End With

Application.EnableEvents = True
End Sub
[/VBA]

[Digita]

Thanks Paul,

I also come up with the following code that does the trick as well:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
For Each cl In Target
cl(1, 2) = cl
Next cl
Application.EnableEvents = True
End Sub

Thanks for your prompt reply. Have a good weekend.

Regards


KP