Cell LOSTFOCUS event

[akn846]

I was looking at the documentation about lostfocus event and it appears that this only appears to ActiveX type controls.

Does anyone know if it is possible to get an event to trigger when a user moves the focus from one cell to another.

Thanks

 

[DerfLongshanks]

As I understand your request try using the selection change event

example
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
MsgBox "ha ha", vbDefaultButton1

End Sub

Derf
If you don't have to run, walk, if you don't have to walk, sit, if you don't have to sit, lie down.

 

[kylua]

The selection change event will not reference the cell you've just left but the change event will, assuming a change has been made to that cell. So if you are wanting to validate the cell in some way, then use the change event.

such as:
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Address = "$B$4" then
if target.value <> 42 then
msgbox &quot;That is not the answer&quot;
end if
end if

End Sub

Target.Column and row etc allows you, well you get the drift.
Of course, Data/Validation would do most validation tasks anyway, that's just an example.

 

[RobBroekhuis]

Or you can use the Selection_Change event and keep a static variable inside the event handler that tracks the previously active cell address.
Rob

 

[akn846]

Thanks for your responses, I must be being really thick.

When I try and paste some of the sample code you've provided into my worksheet I can't get it to run at all - the macro name doesn't appear in the list of available macros within the worksheet, can someone suggest what I am doing wrong.

Thanks

Andy

 

[RobBroekhuis]

You need to put the event handlers on the code for your worksheet or your workbook, not in a normal code module. In the VB editor, right-click on the worksheet you're trying to work with (in the project explorer window in the left of your screen), and select &quot;view code&quot; - this will display in the main window the code module for the worksheet. Put the code from the suggestions above here.
Rob

 

[akn846]

Thanks very much Rob