Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

akn846 (TechnicalUser) (OP)
21 Nov 02 18:26
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.

DerfLongshanks (TechnicalUser)
21 Nov 02 19:39
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.
kylua (TechnicalUser)
21 Nov 02 21:18
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.
RobBroekhuis (TechnicalUser)
21 Nov 02 21:28
Or you can use the Selection_Change event and keep a static variable inside the event handler that tracks the previously active cell address.


akn846 (TechnicalUser) (OP)
22 Nov 02 4:13
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.


RobBroekhuis (TechnicalUser)
22 Nov 02 7:18
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.


akn846 (TechnicalUser) (OP)
22 Nov 02 11:34
Thanks very much Rob

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!

Back To Forum

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