Ex2003

[Randall]

In my previous question perhaps i did not explain my problem fully.
I need to actevate box E38 by entering a no in F38 so that when i enter a
number in box F38 it activates box E38 to deduct box E38 from E37 leaving the
deduted figure in box E38.
Hope this is clearer thank you for all youre help.

 

[Mike H]

Hi,

That would create a circular reference, you need to carry out the
calculation in a different cell like this

=IF(F38="","",E37-E38)

Mike

 

[Gord Dibben]

In order to deduct a value from a cell you need a formula.

Selecting F38 and entering a number will not "activate" E38 to deduct E37 from
E38 and leave the remainder in E38

F38 has nothing to do with E37 and E38 unless one of those cells references F38
in a formula.

You could do it all with event code code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("F38")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Me
..Range("E38").Value = .Range("E37") - .Range("E38")
End With
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that module. Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP