help with worksheet change event
 

What i'd like to do is if a single cell in column I is changed, then in
column J of the same row I want to set the value to Date (today's date).
As a double check column B on the same row must also be populated

It's imperative drag and drop, and delete row events don't try and
populate column J
--
Mike

help with worksheet change event
 

Try this Mike

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count < 1 Then Exit Sub
If Not Application.Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Offset(0, -7).Value < "" Then
Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
End If
End If
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Mike NG" wrote in message ...
What i'd like to do is if a single cell in column I is changed, then in
column J of the same row I want to set the value to Date (today's date).
As a double check column B on the same row must also be populated

It's imperative drag and drop, and delete row events don't try and
populate column J
--
Mike

help with worksheet change event
 

On Mon, 15 Sep 2003 at 22:55:50, Ron de Bruin (Ron de Bruin
) wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count < 1 Then Exit Sub
If Not Application.Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Offset(0, -7).Value < "" Then
Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
End If
End If
End Sub

Superb

Now I have two ways of updating my sheet - either manual input on the
sheet, or by some userforms I have on the page. Is there a way of
saying, "don't run the worksheet_change" event and turn it back on again
later

Cheers
--
Mike

help with worksheet change event
 

Hi Mike

You can use a cell on your worksheet for example with
Yes or No in it to turn it off.

You can use this as first line then in the change event

If Range("A1").Value = "No" Then Exit Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Mike NG" wrote in message ...
On Mon, 15 Sep 2003 at 22:55:50, Ron de Bruin (Ron de Bruin
) wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count < 1 Then Exit Sub
If Not Application.Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Offset(0, -7).Value < "" Then
Target.Offset(0, 1).Value = Format(Now, "mm-dd-yy hh:mm:ss")
End If
End If
End Sub

Superb

Now I have two ways of updating my sheet - either manual input on the
sheet, or by some userforms I have on the page. Is there a way of
saying, "don't run the worksheet_change" event and turn it back on again
later

Cheers
--
Mike

help with worksheet change event
 

On Tue, 16 Sep 2003 at 00:27:19, Ron de Bruin (Ron de Bruin
) wrote:
You can use a cell on your worksheet for example with
Yes or No in it to turn it off.

You can use this as first line then in the change event

If Range("A1").Value = "No" Then Exit Sub

I've just remembered :- Application.EnableEvents = False is what I need
--
Mike