Solved: Reposition Cursor in Particular Text Box

[clhare]

Hi all,

I am working on a userform that has several text boxes on it. I want to set up one of the tex boxes so that if the user types a value that is over the limit, the AfterUpdate event will 1) give a message, 2) remove the value they entered, and 3) reposition the cursor so it is still in that same text box (so they can enter a different value).

How do I get the cursor back into that particular text box? I tried using SetFocus as shown in the following code, but it didn't work. The cursor still goes to the next text box on the form.


If txtPercentage.Value > "50.0" Then
MsgBox "Value cannot exceed 50.0 percent."
frmMyForm.txtPercentage.Value = ""
frmMyForm.txtPercentage.SetFocus
End If

[lucas]

Try it in a before update procedure....
Private Sub txtPercentage_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txtPercentage.Value > "50.0" Then
MsgBox "Value cannot exceed 50.0 percent."
Me.txtPercentage.Value = ""
Me.txtPercentage.SetFocus
End If
End Sub

[clhare]

I tried adding the BeforeUpdate code, but it still jumped to the next text box after I closed the message.

[lucas]

Hi Cheryl, I see what you mean now......let me look at this a minute. I haven't had coffee yet. Seems odd...

[lucas]

Cheryl, I can't get it to work either. The best I have been able to do is get it to work on button click:

Private Sub CommandButton1_Click()
If Me.txtPercentage.Value > "50.0" Then
MsgBox "Value cannot exceed 50.0 percent."
Me.txtPercentage.Value = ""
Me.txtPercentage.SetFocus
End If
End Sub

[TonyJollans]

If you use the Exit Event of the Textbox, it has a Cancel argument; set this to True to cancel exiting (i.e. to stay in) the textbox.

[fumei]

The reason you got it to work with a commandbutton is because you actioning logic on a different control. In other words, the commandbutton control is actioning logic on OTHER controls.

It is difficult to action control self-logic, at least with a final action being to set focus BACK to itself. Event procedures have to end, and in most cases AFTER they end, they move to the next control, regardless of what is in the procedure logic. The main event that is an exception is the _Change event. You can set focus back to the control itself because _Change does NOT end with a change in focus. It is an event of change to the control itself.

Unfortunately, _Change fires every time there is a change. So.....

Sub Textbox1_Change()

...whatever

End Sub


1. user types "5"...._Change fires
2. user types "1" - .Text now "51" - _Changes fires
3. user types "." - .text now "51." - _Changes fires
4. user types "3" - .Text is now "51.3" - _Changes fires


So you COULD, have logic in _Change that would catch it after #2.

Sub txtPercentage_Change()
If txtPercentage.Value > "50.0" Then
MsgBox "Value cannot exceed 50.0 percent."
frmMyForm.txtPercentage.Value = ""
frmMyForm.txtPercentage.SetFocus
End If
End Sub

Demo attached. Click "Show Userform" on the top toolbar.

[fumei]

BTW: the same problem (where the focus IS, and where it GOES) applies to trying to use the _Enter event for the next control.

You could use _Enter on the next control to check BACK to txtPercentage. That is not a problem, and it is not a problem to clear txtPercentage...and the _Enter of the next will even accept a .SetFocus to txtPercentage. BUT...focus will still be put back to the next control (the one you are using _Enter for), because...well, at the end of _Enter, focus IS on that control.

[lucas]

Thanks Tony and Gerry, good information. I tried change but didn't really like the way it behaved but it seems to be fine.

More coffee before I post from now on.

[fumei]

Actually, using Tony's _Exit event may be better for your purposes. It depends. It will only do the test/check when you Tab out of txtPercentage. In other words...when you Exit. It may be a better way in that there is only one procedure executed, rather than multiple _Change executions - for every single change (letter or number) the user enters.

[lucas]

You're right Gerry, this works nicely:
Private Sub txtPercentage_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtPercentage.Value > "50.0" Then
Cancel = True
MsgBox "Value cannot exceed 50.0 percent."
frmMyForm.txtPercentage.Value = ""
frmMyForm.txtPercentage.SetFocus
End If
End Sub

Thanks to Tony for that tip.

[fumei]

But you DO have to Tab out so that _Exit fires.

[lucas]

Actually, enter seems to work also Gerry. See attached.

[clhare]

Awesome! The Exit event works great--whether I tab out of the text box or use the mouse! Thanks so much!!

[jblix]

I was glad to find this post. It speaks to what I've been working on as well. The only glitch I had when using the exit event was that I couldn't quit out of the form (unload me) without entering a dummy value in the text box. But I'm still a newbie. :)