Workbook locks up after Worksheet_Change operation

[Philosophaie]

Column "D" is subtracted from "E" the previous total and simulaneously "F" is
added to the mix whenever columns "D,E or F is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oTarget As Range
Dim oIntersect As Range
Set oTarget = Range("D1:F100")
Set oIntersect = Application.Intersect(oTarget, Target)
If Not oIntersect Is Nothing Then
With Sheets("Sheet1")
For n = 2 To 100
.Cells(n, 5) = .Cells((n - 1), 5) + .Cells(n, 6) - .Cells(n,
4)
Next n
End With
End If
End Sub

 

[Ryan H]

Because you are changing cell value in the Worksheet Change Event the Event
is refired over and over to infinite. You have to disable events before you
change cells. Hope this helps! If so, let me know, click "YES" below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oTarget As Range
Dim oIntersect As Range

Set oTarget = Range("D1:F100")
Set oIntersect = Application.Intersect(oTarget, Target)

Application.EnableEvents = False

If Not oIntersect Is Nothing Then
For n = 2 To 100
With Sheets("Sheet1")
.Cells(n, 5) = .Cells((n - 1), 5) + .Cells(n, 6) - .Cells(n,
4)
End With
Next n
End If

Application.EnableEvents = True

End Sub

 

[Philosophaie]

When I add the code below for Sheet2 it does not work but if you leave
oTarget2 as oTarget the fire with the Sheet1 range:

Dim oTarget2 As Range
Dim oIntersect2 As Range
Set oTarget2 = Sheets("Sheet2").Range("D1:F25")
Set oIntersect2 = Application.Intersect(oTarget2,
Target)Application.EnableEvents = False
If Not oIntersect2 Is Nothing Then
With Sheets("Sheet2")
For n = 2 To 25
.Cells(n, 5) = .Cells((n - 1), 5) + .Cells(n, 6) - .Cells(n,
4)
Next n
End With
End If
Application.EnableEvents = True

 

[Ryan H]

1.) Always put Option Explicit at the top of your module. This will point
out a lot of potential issues with your code in the future before the code is
executed.

2.) Always declare your variables. I noticed you don't have the variable n
declared.

3.) This code worked for me. Make sure this code is place in the Sheet2
module.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oTarget2 As Range
Dim oIntersect2 As Range
Dim n As Long

Set oTarget2 = Sheets("Sheet2").Range("D1:F25")
Set oIntersect2 = Application.Intersect(oTarget2, Target)

Application.EnableEvents = False

If Not oIntersect2 Is Nothing Then
With Sheets("Sheet2")
For n = 2 To 25
.Cells(n, "E") = .Cells(n - 1, "E") + .Cells(n, "F") -
..Cells(n, "D")
Next n
End With
End If

Application.EnableEvents = True

End Sub

4.) If this code doesn't work be specific on why it doesn't work so we can
debug the code. Specifics like, where an error occurs in your code?,
description of the error occurs?

Hope this helps! If so, let me know, click "YES" below.

 

[Philosophaie]

Where does the Worksheet_Change subroutine belong:

[Sheet1(Sheet1)]
[Sheet2(Sheet2)]
[ThisWorkbook]

 

[Dave Peterson]

It belongs in the module of the sheet that you want to be processing. Sot that
means it has to be the first or second in your list

Are you looking for changes in Sheet1? Then it belongs in the top one.
Are you looking for changes in Sheet2? Then it belongs in the middle one.

It never belongs in the bottom one (ThisWorkbook).

Where does the Worksheet_Change subroutine belong:

[Sheet1(Sheet1)]
[Sheet2(Sheet2)]
[ThisWorkbook]

 

[Dave Peterson]

You can change cells on the same worksheet, different worksheets, worksheets in
different workbooks. Heck, you can open a workbook (or create a new workbook)
and do whatever you like.

If you don't want the worksheet_change event to fire when you change a cell on
the same sheet, you can turn off events...

application.enableevents = false
me.range("A1").value = "something you want"
application.enableevents = true

first the workshet change must of been in the correct location for the
workbook to lock up on you origianlly. Second a worksheet change isn't
meant to change cells other than the target cell. Excel doesn't like
when you change other locatiions than the target. I doesn't allow you
to change cells in other sheets and only sometimes lets you change cells
on the same sheet.

I don't know why you just don't use a formula on the worksheet

Cells(n, 5) = .Cells((n - 1), 5) + .Cells(n, 6) - .Cells(n,4)

Put the following formula in cell E2

=E1+F2-D4

Then copy cell E2 to cells E3 to E100.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=173413

Microsoft Office Help