circular reference

[enyaw]

I have one cell for total time and another cell for total downtime. When a
user enters a number into either total time or into downtime i need to
automatically subtract the total downtime from the total time. The problem
is circular reference as the user has to enter the value into total time
before the calculation is made.

Any help would be appreciated.

 

[Pete Rooney]

Hi, Enyaw,

Try pasting this code into the codesheet for the worksheet containing your
cells.
You may want to change the worksheet name and change the range names to cell
references, but it's easier if you apply the names "Total" and "DownTime" to
the two cells in question, then you don't need to change the code!

Pete

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Total As Range
Set Total = Sheets("Sheet1").Range("Total")
Dim TotalIntersection As Range
Set TotalIntersection = Intersect(Target, Total)

Dim DownTime As Range
Set DownTime = Sheets("Sheet1").Range("DownTime")
Dim DownTimeIntersection As Range
Set DownTimeIntersection = Intersect(Target, DownTime)

On Error GoTo ErrorExit

Application.EnableEvents = True

If Not TotalIntersection Is Nothing Then 'If you change the Total value
Application.EnableEvents = False
Total.Value = Total.Value - DownTime.Value
Application.EnableEvents = True
End If

If Not DownTimeIntersection Is Nothing Then 'If you change the DownTime
value
Application.EnableEvents = False
Total.Value = Total.Value - DownTime.Value
Application.EnableEvents = True
End If

ErrorExit:
Exit Sub

End Sub

 

[Pete Rooney]

Actually, this is a little neater:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Total As Range
Dim TotalIntersection As Range
Set Total = Sheets("Sheet1").Range("Total")
Set TotalIntersection = Intersect(Target, Total)

Dim DownTime As Range
Dim DownTimeIntersection As Range
Set DownTime = Sheets("Sheet1").Range("DownTime")
Set DownTimeIntersection = Intersect(Target, DownTime)

On Error GoTo ErrorExit

If Not TotalIntersection Is Nothing Or Not DownTimeIntersection Is
Nothing Then
Application.EnableEvents = False
Total.Value = Total.Value - DownTime.Value
Application.EnableEvents = True
End If

ErrorExit:
Exit Sub

End Sub