Excel functions for cell "unfocus"?

Is there a way to run a subroutine on a cell "after" the user has entered in a value and left the cell?

Right now I have the cell set to ask for user input and calculate the estimated value. I would like to avoid the use of an InfoBox but cannot seem to figure out a way to run functions on a cell after the user enters a value and leaves.

Any ideas?

if i am not mistaken the SelectionChange event of a worksheet will do that thing? is that what you want?

It is but I can only figure out how to get to to calculate the cell that is selected and if there is no information within that cell to begin with, obviously nothing will be calculated. This is why I am currently using an InputBox to request an initial value... run the calculation and then output the new value.

Here is what I have:


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("calc")) Is Nothing Then
x = InputBox(Prompt:="Credit Hours", Title:="Enter Total Credit Hours", Default:="0")
Target.Select
Selection.Value = ""
Selection.Value = x * Range("D25").Value
End If
End Sub


Not sure why I have a Selection.Value = "" in there though. :)




Please post Excel questions, in the Excel forum.

Thank you.

Basically your problem is that 'Target' is not the cell/range you just came from. I want you to figure this out yourself but, at the same time, it's not that easy to explain.... I'll give it my best shot. I haven't tested this so I hope I haven't missed anything...

The key here is working out the range/cell the user came from. You can so this in the 'previously' triggered Worksheet_SelectionChange event. Sounds odd but I will elaborate:

At the end of your Worksheet_SelectionChange event procedure you have to assign an activecell reference to a public level variable. Public level variables retain their values until the workbook is closed so the information will not be lost when your SelectionChange procedure finishes. I suggest public and not module level because I think you're also going to have to trap the workbook_open event (I'll explain later).

So now, when the event is triggered again you can refer to this variable in order to know which cell you should be working on. I would also suggest that you record the value of that cell so that you can determine whether or not the user actually updated the value in the cell or just selected it (I presume that you only want to perform the calculation if the user entered a number into the cell).

But what about the very first SelectionChange event? Your public level range variable will be nothing. Well, to avoid that you should use the Worksheet_Activate event to assign the activecell reference to your range variable when the worksheet is activated.

But what about when the workbook is opened? I think you will need to capture the workbook_open event and add a line to activate your sheet and assign the activecell reference to your range variable here too because otherwise you will have a problem if your sheet is the activesheet when the workbook is opened (the worksheet_activate event will not be triggered).

I hope that makes sense (and I hope I haven't missed something obvious!).

Colin