Excel VBA - not doing what I expect with SetFocus 1

[Chats]

I have some VBA code with a userform. When the user clicks out of a particular field on the form, I want to run some validation code, and if the entry is invalid, shade the box, show a message and then return the cursor to the textbox.

I have pasted my code below (cut down to keep things simple here). The trouble is that the SetFocus doesn't seem to work (the cursor ends up in whatever textbox the user clicked out to.

If I do a setfocus to another textbox on the form it works fine and the cursor moves where I tell it to go - what is going on here?

This is my code

Private Sub ReportDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

ReportDate.BackColor = vbRed
MsgBox("error message")
ReportDate.SetFocus

End Sub

Any help would be great as this makes no sense to me why it won't work!

Cheers

Ade

 

[DonU]

You need to set the Cancel var. to True to not allow the update to the field & thus leave the cursor in the field. Don't bother with the SetFocus.