Simple code makes Excel 2007 crash
 

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

Simple code makes Excel 2007 crash
 

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

Simple code makes Excel 2007 crash
 

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.

Simple code makes Excel 2007 crash
 

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.

Simple code makes Excel 2007 crash
 

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

Simple code makes Excel 2007 crash
 

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

Simple code makes Excel 2007 crash
 

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