Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Cell LOSTFOCUS event

Cell LOSTFOCUS event

Cell LOSTFOCUS event

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.


RE: Cell LOSTFOCUS event

As I understand your request try using the selection change event

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

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.

RE: Cell LOSTFOCUS event

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 "That is not the answer"
 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.

RE: Cell LOSTFOCUS event

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


RE: Cell LOSTFOCUS event

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.



RE: Cell LOSTFOCUS event

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 "view code" - this will display in the main window the code module for the worksheet.  Put the code from the suggestions above here.


RE: Cell LOSTFOCUS event

Thanks very much Rob

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close