SetFocus not doing what I expect on userform 2

[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

ps, I posted this to MSOffice forum in error earlier on - just in case anybody spotted my mistake!

 

[PHV]

You may try something like this:
Private Sub ReportDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ReportDate.BackColor = vbRed
MsgBox("error message")
Cancel = True
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi Chats,

You should really put validation in the BeforeUpdate event, but whatever event you use you must set Cancel = True if it fails.

The trouble with what you are doing is that you are not stopping the sequence of events which follows your validation. At the time your code runs, ReportDate has the focus and your setting it has no effect. After your code runs the focus is shifted somewhere else.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.