Hello All,

I use this code to check if a value changes in a specific column and update
the adjacent cell (one to the right) with today's date.
However, it makes Excel 2007 crash.

Any advice?


Private Sub Worksheet_Change(ByVal Target As Range)
If ((ActiveCell.Column = 6) And (ActiveCell.Row 5)) Then
ActiveCell.Offset(0, 1).Value = Str(Date)
End If
End Sub

Thanks!
Mathieu

Hi Mathieu

No problem here
Do you have the same problem in a new workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mathieu" schreef in bericht ...
Hello All,

I use this code to check if a value changes in a specific column and update
the adjacent cell (one to the right) with today's date.
However, it makes Excel 2007 crash.

Any advice?


Private Sub Worksheet_Change(ByVal Target As Range)
If ((ActiveCell.Column = 6) And (ActiveCell.Row 5)) Then
ActiveCell.Offset(0, 1).Value = Str(Date)
End If
End Sub

Thanks!
Mathieu

On Apr 7, 5:28*pm, "Ron de Bruin" wrote:
Hi Mathieu

No problem here
Do you have the same problem in a new workbook

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Mathieu" schreef in . ..



Hello All,

I use this code to check if a value changes in a specific column and update
the adjacent cell (one to the right) with today's date.
However, it makes Excel 2007 crash.

Any advice?

Private Sub Worksheet_Change(ByVal Target As Range)
If ((ActiveCell.Column = 6) And (ActiveCell.Row 5)) Then
* *ActiveCell.Offset(0, 1).Value = Str(Date)
End If
End Sub

Thanks!
Mathieu- Hide quoted text -

- Show quoted text -

Hi Mathieu,

I can't see anything wrong with that either, but I would suggest using
the 'Target' range rather than Activecell to test what has changed.
They should both always be the same, but there is a chance that they
won't be... for example if another macro changes a value in column F,
you would presumably still want to catch it but the active cell could
be anything. I'd suggest:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells(1), _
Range("F6:F65536")) Is Nothing Then
Target.Cells(1).Offset(0, 1).Value = Str(Date)
End If
End Sub

Cheers,
Ivan.

Hi,

The OP wanted to check the full column so 65536 doesn't work in Excel 2007

Mike

"Ivyleaf" wrote:

On Apr 7, 5:28 pm, "Ron de Bruin" wrote:
Hi Mathieu

No problem here
Do you have the same problem in a new workbook

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Mathieu" schreef in . ..



Hello All,

I use this code to check if a value changes in a specific column and update
the adjacent cell (one to the right) with today's date.
However, it makes Excel 2007 crash.

Any advice?

Private Sub Worksheet_Change(ByVal Target As Range)
If ((ActiveCell.Column = 6) And (ActiveCell.Row 5)) Then
ActiveCell.Offset(0, 1).Value = Str(Date)
End If
End Sub

Thanks!
Mathieu- Hide quoted text -

- Show quoted text -

Hi Mathieu,

I can't see anything wrong with that either, but I would suggest using
the 'Target' range rather than Activecell to test what has changed.
They should both always be the same, but there is a chance that they
won't be... for example if another macro changes a value in column F,
you would presumably still want to catch it but the active cell could
be anything. I'd suggest:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target.Cells(1), _
Range("F6:F65536")) Is Nothing Then
Target.Cells(1).Offset(0, 1).Value = Str(Date)
End If
End Sub

Cheers,
Ivan.

Did you put an entry in Column 6 with Row 5 when you tested it? I ask because that code crashes for me also and I think the reason is the unchained set of Worksheet_Change event calls that the first entry initiates.

Rick


"Ron de Bruin" wrote in message ...
Hi Mathieu

No problem here
Do you have the same problem in a new workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mathieu" schreef in bericht ...
Hello All,

I use this code to check if a value changes in a specific column and update
the adjacent cell (one to the right) with today's date.
However, it makes Excel 2007 crash.

Any advice?


Private Sub Worksheet_Change(ByVal Target As Range)
If ((ActiveCell.Column = 6) And (ActiveCell.Row 5)) Then
ActiveCell.Offset(0, 1).Value = Str(Date)
End If
End Sub

Thanks!
Mathieu

Your post just made me realized that this was a rookie-mistake!

When you change the value of the adjacent cell using offset, this doesn't
change which cell is activated, so the 'IF' condition is still true, which
starts a recurring loop.

Changed the code to this crappy version:

If ActiveCell.Column = 6 And ActiveCell.Row 5 Then
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = Date
End If

Thanks All for your help!
Mathieu


"Rick Rothstein (MVP - VB)" wrote in
message ...
Did you put an entry in Column 6 with Row 5 when you tested it? I ask
because that code crashes for me also and I think the reason is the
unchained set of Worksheet_Change event calls that the first entry
initiates.

Rick


"Ron de Bruin" wrote in message
...
Hi Mathieu

No problem here
Do you have the same problem in a new workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mathieu" schreef in bericht
...
Hello All,

I use this code to check if a value changes in a specific column and
update
the adjacent cell (one to the right) with today's date.
However, it makes Excel 2007 crash.

Any advice?


Private Sub Worksheet_Change(ByVal Target As Range)
If ((ActiveCell.Column = 6) And (ActiveCell.Row 5)) Then
ActiveCell.Offset(0, 1).Value = Str(Date)
End If
End Sub

Thanks!
Mathieu

On Apr 7, 3:14*pm, "Mathieu" wrote:
Hello All,

I use this code to check if a value changes in a specific column and update
the adjacent cell (one to the right) with today's date.
However, it makes Excel 2007 crash.

Any advice?

Private Sub Worksheet_Change(ByVal Target As Range)
*If ((ActiveCell.Column = 6) And (ActiveCell.Row 5)) Then
* * ActiveCell.Offset(0, 1).Value = Str(Date)
*End If
End Sub

Thanks!
Mathieu

Hi,

Did this problem lead to damage of your excel file? If so, I think you
can try a utility called Advanced Excel Repair to repair your Excel
xls file. It works rather well for my corrupt Excel xls files. Its web
address is http://www.datanumen.com/aer/

Hope this helps.

Alan