can we execute a formula without moving cursor from the cell? After giving data in to Input cell either we press enter or we move from that cell and then any formula related to that gets executed. Is there any way that while entering input itself we can see the result in output in excel?Please help me in finding this option.
+2
A:
I don't believe it's possible for code to execute while the "editor" of a cell has focus and control. For example, try doing something else in Excel while you are editing a formula. I believe that the cell has to finish saving its value or formula before code can execute.
You can have a formula "listen" for when a cell is updated, but this is a little bit complicated. It could then force you back into the original cell once it is updated. Here's how you can write some code to "listen" for when a value of a cell changes. In the following example:
- I record the value of Cell
I1tostrValue. - Then I run a loop that stays running as long as the value of cell
I1has not changed. - The
DoEventscommand releases control back to the processor, which is what allows you to work in Excel while this is running. - Once the value of cell
I1changes, theWhilestatement will be false and the loop will exit. - After the loop exits, a message box pops up that tells you the value changed and then the original cell is selected again.
'
Sub testChange()
Dim strValue As String
strValue = Range("I1").Value
Do While strValue = Range("I1").Value
DoEvents
Loop
MsgBox "The value changed!"
Range("I1").Activate
End Sub
Perhaps if you simply wanted to return control back to the original cell every time the value changed, you could use the line above without the message box and return to the loop once your method executes. Keep in mind that you might have to figure out another way to exit your code if you do that.
Why do you want to have it execute from within the cell? What is the end purpose of your task?
Ben McCormack
2009-11-26 04:33:42
As far as I know, the only event you have available is Worksheet_Change(ByVal Target As Range), which is triggered when the change is complete, i.e. when you leave the target. What would you listen to in the scenario you describe? It sounds to me that you would need an event which I don't think exists, like changing.
Mathias
2009-11-26 04:51:16
@Mathias - see my example above. it uses `DoEvents` and a loop to "listen" to see when the value of a cell changes.
Ben McCormack
2009-11-26 05:04:32
Testing your macro in Excel 2003, the MsgBox only triggers after you press Enter to finish editing the cell. The Value property doesn't update until then
barrowc
2009-11-27 04:30:57
@barrowc - you are correct. I don't believe there's a way to trigger a method's execution until *after* it has been updated. However, the procedure itself can force the focus back to the cell if you want to immediately try a new value. Granted, this doesn't help you if you want to execute something in the style of an "auto-complete suggestion list" or something like that.
Ben McCormack
2009-11-27 15:55:50
A:
Press F9 to do a re-calc without moving from the input cell. This works in Excel 2007. I'm not sure about earlier versions.
Mike Fitzpatrick
2009-12-07 01:59:28