Calculation of cell value with worksheet change event

[Raj]

Hi,

I have an integer in cell a1. When an integer is input in cell b1, I
need the value in cell a1 multiplied by the value in cell b1 and the
result placed in cell a1. To achieve this I am using the
Worksheet_change event with target range as b1. The program works ok
when the user inputs the value in cell b1 and then presses the Enter
key. However, if the user clicks outside cell b1 after inputting the
value in cell b1, then the value in cell a1 does not change. How do I
ensure that the calculation takes place when the user exits b1?

How can I achieve this through a worksheet event?. Is there an event
that fires when the cell is exited?
Is there any other way to do this?

Thanks in advance for the help.

Regards,
Raj

 

[aidan.heritage]

Hi,

I have an integer in cell a1. When an integer is input in cell b1, I
need the value in cell a1 multiplied by the value in cell b1 and the
result placed in cell a1. To achieve this I am using the
Worksheet_change event with target range as b1. The program works ok
when the user inputs the value in cell b1 and then presses the Enter
key. However, if the user clicks outside cell b1 after inputting the
value in cell b1, then the  value in cell a1 does not change. How do I
ensure that the calculation takes place when the user exits b1?

How can I achieve this through a worksheet event?. Is there an event
that fires when the cell is exited?
Is there any other way to do this?

Thanks in advance for the help.

Regards,
Raj

Use the selection change event instead - this returns as a target the
cell that has just been left

 

[Gary''s Student]

Private Sub Worksheet_Change(ByVal Target As Range)
Set b1 = Range("B1")
If Intersect(Target, b1) Is Nothing Then Exit Sub
Application.EnableEvents = False
'
' update A1
'
Application.EnableEvents = True
MsgBox ("B1 changed")
End Sub

Should work if B1 is refreshed with a value followed by ENTER, or by a
mouseclick, or by an ARROW movement.