Excel VBA Programming...How to Trigger an Event WhenYou Move Off a Cell
 

Hello,

I've got what I think is a "simple problem" for Excel VBA, but can't
seem to get it working exactly the way I want. Here's a simple example
of what I'd like to do:

1) You have a Number in Cell: B2
2) You have another Number in Cell: C2
3) You calculate the result and put it in Cell: E2

I'd like to be able to enter numbers in Cells B2 and C2, and when I
"Move Off" either Cell B2 or C2, have the sum (ie: B2 + C2) calculated
and the result put in Cell E2. I'd like this to happen if I "Key Off"
(ie: up or down arrow) either B2 or C2, or "Mouse Click Off" (ie:
highlight Cell D4 and click on the mouse), or click on the "Delete Key"
on the Keyboard.

Is there an easy way to do this in VBA? The ActiveCell Property in
this case will give me B1 if I click on the "Up Arrow" after being on
B2, and B3 if I click on the "Down Arrow"... Since I don't know the
direction I've come from, I can't determine it was B2 in this
particular case...

Thanks a Bunch...

Excel VBA Programming...How to Trigger an Event WhenYou Move Off aCell
 

Why not just put a formula in E2:
=b2+c2
(or some variation of that)

====
But if you're looking for some background....

I think I'd use the worksheet_change event. This event will fire if you type
something and then hit enter (or move to another cell).



wrote:

Hello,

I've got what I think is a "simple problem" for Excel VBA, but can't
seem to get it working exactly the way I want. Here's a simple example
of what I'd like to do:

1) You have a Number in Cell: B2
2) You have another Number in Cell: C2
3) You calculate the result and put it in Cell: E2

I'd like to be able to enter numbers in Cells B2 and C2, and when I
"Move Off" either Cell B2 or C2, have the sum (ie: B2 + C2) calculated
and the result put in Cell E2. I'd like this to happen if I "Key Off"
(ie: up or down arrow) either B2 or C2, or "Mouse Click Off" (ie:
highlight Cell D4 and click on the mouse), or click on the "Delete Key"
on the Keyboard.

Is there an easy way to do this in VBA? The ActiveCell Property in
this case will give me B1 if I click on the "Up Arrow" after being on
B2, and B3 if I click on the "Down Arrow"... Since I don't know the
direction I've come from, I can't determine it was B2 in this
particular case...

Thanks a Bunch...

--

Dave Peterson

Excel VBA Programming...How to Trigger an Event WhenYou Move Off a
 

Here is some selection change code. It calculates whenever the active cell is
moved.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("E2").Value = Range("C2").Value + Range("B2").Value
End Sub

Right click the sheet tab and select view code. Paste the code above... Not
you are going to loose you undo function with this code and it does not
recalc when delete is pressed (we can do this with on change code if you
wish)...

--
HTH...

Jim Thomlinson


" wrote:

Hello,

I've got what I think is a "simple problem" for Excel VBA, but can't
seem to get it working exactly the way I want. Here's a simple example
of what I'd like to do:

1) You have a Number in Cell: B2
2) You have another Number in Cell: C2
3) You calculate the result and put it in Cell: E2

I'd like to be able to enter numbers in Cells B2 and C2, and when I
"Move Off" either Cell B2 or C2, have the sum (ie: B2 + C2) calculated
and the result put in Cell E2. I'd like this to happen if I "Key Off"
(ie: up or down arrow) either B2 or C2, or "Mouse Click Off" (ie:
highlight Cell D4 and click on the mouse), or click on the "Delete Key"
on the Keyboard.

Is there an easy way to do this in VBA? The ActiveCell Property in
this case will give me B1 if I click on the "Up Arrow" after being on
B2, and B3 if I click on the "Down Arrow"... Since I don't know the
direction I've come from, I can't determine it was B2 in this
particular case...

Thanks a Bunch...