PDA

View Full Version : Solved: Change event



Digita
06-28-2007, 06:23 PM
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
06-28-2007, 07:32 PM
Try this


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

Digita
06-28-2007, 08:44 PM
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