MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

SetFocus question

  Asked By: Abbie    Date: Mar 04    Category: MS Office    Views: 1525

Hope somone can explain what i'm doing wrong...

Userform1 has combobox1 and textbox1... User selects a code from
combobox1 and enters a date in textbox1. What I'm trying to do is:
1. Reject the date entered in textbox1 if combobox1.text=1.
2. Move the cursor back to combobox1 (tab index for combobox=1 &

I'm trying:

Private Sub textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If combobox1 = "1" Then
MsgBox "You must change the code to" & Chr$(10) & _
"something other than '1=Pending'" & Chr$(10) & _
"when entering a date."
Exit Sub
'other code
End If

MsgBox appears as expected, but after "OK"ing the msgbox, the cursor
has moved forward to the next field (tab index=3) rather than back
to combobox1.

I'm sure it must be something simple, but I'm stumped... Any



4 Answers Found

Answer #1    Answered By: Felix Gray     Answered On: Mar 04


Answer #2    Answered By: Sultana Tabassum     Answered On: Mar 04


is wrong. The Controls index  should be a string naming the control, or an
integer index into the controls collection. But why not simply do


Please also change  your

If combobox1 = "1" Then


If combobox1.Value = "1" Then

(If nothing else, this will remind you that you are dealing with an object
that has attributes and methods available.)

The event handler almost certainly got into trouble at the
Controls(combobox1) call. It possibly took the value from the combo box (a
string "1"), converted it to an integer, and set the focus to the first
control. Or it might just have crashed looking for a control called "1".

However, there is also the fact that you are doing this from a text box exit
event, and the move  that caused the exit might happen after the exit event
completes. You might need to set the event's "Cancel" parameter to true to
get it not to do the move. Try without it first, but put it in if you're
still having trouble.

BTW, you don't need Exit Sub in your If statement. Without it, execution
will reach the Else, then jump past the End If and reach the End Sub - which
is what you want to achieve.

Answer #3    Answered By: Hollie Hughes     Answered On: Mar 04

you are correct about the missing quotes... Unfortunately, that was
just an editing error on my part when I tried to simplify the posted code.
Actual code  does have the control name quoted, but is still is causing problems.

Answer #4    Answered By: Jackson Williams     Answered On: Mar 04

Thanks for taking the time to offer such a detailed response and set of
suggestion. Also accept my apologies for this delayed response to your reply.

My error with the control index  (also pointed out by Pascal), was actually an
editing error on my part when I tried to simplify the post. My actual code  was:

You also offered the comment :
".However, there is also the fact that you are doing this from a text box
exit event, and the move  that caused the exit might happen after the exit event

This proved to be the thing I need to resolve my problem. With a clear head the
next morning, I discovered (through careful observation as I manually stepped
through the code) that your comment was right on the mark. The problem proved
to be with using the <Tab> key when moving through the cells... Even though
SetFocus was been executed, the Exit Event insisted on issuing the <Tab>.

Because of some "Enter Event" code associated with combobox1 (or further
ignorance on my part), I had no success with "Cancel = True" in the textbox1
Exit Event. The code executed the line without error, but the <Tab> still

I finally ended up moving the conditional test to the textbox1 Enter Event and
added a Boolean flag that is tested/trapped in the combobox1 Enter Event. This
finally resolved my problem.

Didn't find what you were looking for? Find more on SetFocus question Or get search suggestion and latest updates.