Prev: Repeat a macro until the last line is empty or repeat it 1400 time
Next: Pastespecial Operation:=xlNone
From: enyaw on 18 Jul 2008 05:18
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.
From: Pete Rooney on 18 Jul 2008 06:06
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




"enyaw" wrote:

> 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.
From: Pete Rooney on 18 Jul 2008 06:14
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


"enyaw" wrote:

> 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.
 | 
Pages: 1
Prev: Repeat a macro until the last line is empty or repeat it 1400 time
Next: Pastespecial Operation:=xlNone