MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

2003 - Setfocus on userform

  Asked By: Hisham    Date: Nov 24    Category: MS Office    Views: 2433

Can someone confirm this for me if you have a few minutes please?

I have a userform with a textbox and a listbox.
I want to type into the text box and put the contents in the listbox when
the user presses return.

The code I have so far is... In the KeyDown event...

Private Sub txtStringLineInput_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 13 Then
Me.lstStringSetData.AddItem Me.txtStringLineInput.Text
Me.txtStringLineInput.Text = " "
End If

End Sub

Problem is I can't seem to rest the focus back to the textbox for another
The focus seems to be retained by the listbox.



21 Answers Found

Answer #1    Answered By: Nina Garcia     Answered On: Nov 24

without trying your code, I have another approach to suggest as an
alternative to the KeyDown event, which is to put  a CommandButton on the
form, and set its "Default" property to True (so it gets "Clicked" when the
user presses Enter), and on its Click event  add the TextBox contents  to the
ListBox and then set the focus  back to the TextBox. I THINK this would work.
As long as you don't mind having this new CommandButton there, and/or don't
already have a nother Default CommandButton...

Answer #2    Answered By: Wilbert Patterson     Answered On: Nov 24

Tried it and it's a no go 'cause lotsa events aren't firing!

Answer #3    Answered By: Faeezah Khan     Answered On: Nov 24

If you've tried solution and seen that it works, here's why I think
it does. -- is that English -- Hmmmmm....

The code  that's doing the work isn't in a text  box event  any more.

He's moved it to the default command button.

It's pretty neat really and removes the need to use ... Ugh ... Sendkeys.

I like both solutions but as I have 2 pages in the form and similar
arrangements on both then I'll probably go with the sendkeys solution.

Answer #4    Answered By: Kanya Jainukul     Answered On: Nov 24

Yes, I've come across something like this before. It's not intuitive.
Would something like this do what you're looking for?:

Private Sub txtStringLineInput_BeforeUpdate(ByVal Cancel As
Me.lstStringSetData.AddItem Me.txtStringLineInput.Text
Me.txtStringLineInput.Text = ""
Cancel = True
End Sub

Answer #5    Answered By: Connie Wallace     Answered On: Nov 24

And wouldn't you know it.... Hours of searching has only produced the
infamous "Microsoft has confirmed this to be a bug" message!!!!

Ah well... There's always the API.

Answer #6    Answered By: Adelino Fischer     Answered On: Nov 24

Okay guys... Found a solution with a minimum of inconvenience... But it may
not work for all 'coz my form only has the listbox  and textbox  on it.

If I remove the tabstop on the listbox... It works.

I'm still lookiing though 'coz I'd like a general solution for more controls
on the form...

Answer #7    Answered By: Joseph Evans     Answered On: Nov 24

I HATE to suggest this, but... how about using SendKeys to send a Shift-Tab?
This worked every time for me, even after adding additional controls to the form
and changing the tab order several different ways.

Private Sub txtStringLineInput_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
If KeyCode = 13 Then
If Len(Me.txtStringLineInput.Text) > 0 Then
Me.lstStringSetData.AddItem Me.txtStringLineInput.Text
End If
Me.txtStringLineInput.Text = vbNullString
Application.SendKeys "+{TAB}"
End If
End Sub

Private Sub UserForm_Initialize()
End Sub

Answer #8    Answered By: Janis Hart     Answered On: Nov 24

I'd even consider the.... Shudder..... Sendkeys at this point. Problem is...
Where to put  it. Tried your suggested place and it didn't work ... There's
just code  going into black holes right now.

Answer #9    Answered By: Shiv Patel     Answered On: Nov 24

I just replaced the SetFocus line in your original code  with SendKeys command.
Per your reply to Pascal, I added a multipage control to the form and moved the
other controls onto Page1. Still works every time for me - unless I set the
TabStop property to False for txtStringLineInput.

Answer #10    Answered By: Marnia Mian     Answered On: Nov 24

I'm probably having a blonde evening then. AAAARRRGGGGHHH!!! I soooo hate it
when that happens!!!

Do you mind awfully sending your test file to me at all please?

You and anyone else is of course welcome to my code  so far.

Answer #11    Answered By: Ayaz Hashmi     Answered On: Nov 24

Did you check that last, possibly important:
"unless I set the TabStop property to False for txtStringLineInput."

The flag checking code  should go into the enter event  for the control
that would normally have the focus  after pressing the Return key. If
there's no tab stop then that control won't be entered, so no Enter

Answer #12    Answered By: Zane Thompson     Answered On: Nov 24

You marvelous man!!!!

It works!!!

The reason it "didn't work" at first was that I had some test code  left

Private Sub lstStringSetData_Enter()
End Sub

For some reason this was giving the focus  to the page the controls were on.

Go figure.

Answer #13    Answered By: Constance Reid     Answered On: Nov 24

I think I've found a solution based on some closer investigation
outlined below. The solution which first came to mind and worked was
to have a flag (which I set up at the top of the userform's code
module) that was used to set the focus  back to the text  box if the
flag was set in the first location I found setfocus  not to be ignored
by Excel. So this is what became of code  (and it should work
with more than 2 controls on the form):

Dim Wrong As Boolean 'the flag

Private Sub txtStringLineInput_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Wrong = True
Me.lstStringSetData.AddItem Me.txtStringLineInput.Text
Me.txtStringLineInput.Text = ""
End If
End Sub

Private Sub lstStringSetData_Enter()
If Wrong Then
Wrong = False
End If
End Sub

Answer #14    Answered By: Este Ferrrari     Answered On: Nov 24

I did something similar and found that a lot of the event  handlers were just
being ignored as well.

It's been very frustrating.

And... I'm using a multipage control as well which seems to complicate
things a bit 'cause I can't seem to get to the tabstop property of a
page.... I was still going down the tabstop route.

I'd set up a seperate userform  for testing with all the default name and so
on. The tabstop thing worked ok there but when I transfered it to the "real"
form using the multipage it broke.


I copied and set up your code  with the flag... But... When I run it the
focus shifts to the multipage control the textbox  is on

Answer #15    Answered By: Channarong Boonliang     Answered On: Nov 24

I have sort of a similar problem with a userform  with a listbox  of
employees. I read thru all this thread, and I tried several things
that seemed to work for Lisa, but it didn't work in my userform.

I have a list of employess with starting with their employee number
in the first column, name in the second, what team they are on in
the 3rd column. Using the listbox click event, clicking on any of
them puts the selected information in 3 textboxs to the side, so
they can edit the info/make corrections. I have commandbuttons for
moving up and down the list "Next" and "Previous", so the supervisor
can scroll through the list and check associated details. (I also
have a sub to add line to the listbox to add a new employee).

All of that works fine, no problem except for one little thing,
which will only be a irritation if a supervisor has a long list of

The problem is that when I click on one of the editing textboxes,
like for example the one for "Employee Name" . . . and make a
spelling correction, the textbox  change event  fires to correct the
name on the spread sheet and the listbox jumps back  to the first
name at the top of the list. I tried about 4 or 5 ways to get it to
reset back to the name that I'd just corrected (for example the name
at ListIndex 31) and it doesn't work. The name remains showing in
the textbox, but, the list box  is scrolled all the way back to the

So, the supervisor has to scroll all the way back down to the name
to continue checking the list. As I said, it's a minor irritation,
but for someone with a hundred employees or thousands, it would get

Please help. I can send portions of code, if that helps, but I'm
not sure what portions you'd like to see, so let me know and I'll
send it.

Answer #16    Answered By: Abagail Cohen     Answered On: Nov 24

I believe I do just what you desire. I simply set the ListIndex
and it works for me. I do it for a serial port set up UserForm and if
the cancel button is clicked, the previous values are "re-highlighted"
in the list box.
The code  you see here is the entire sub code when a cancel button is

The first two lines of code below reset two list boxes back  to the
"before change" values and highlights them. If the list has been
scrolled away from those values so that the original value is no
longer visible, they scroll back so they are shown. The values that
were loaded into the list boxes when the UserForm is initialized are
the values in those same Sheet4 Ranges, therefore any changes are aborted.

I do not know, however, if the repaint is required.

This is on Excel 97.

RE the Wait:
I also show a short "changes accepted" or "chenges ignored" text
for that Wait period.

- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Public Sub DontChange()

'Restore Port
PortVFO1.ListIndex = Sheet4.Range("ComPortVFO_1") - 1

'Restore Baud
BaudVFO1.ListIndex = Sheet4.Range("Baud_1") - 1

Me.ChangesCancel.Visible = True
Wait (100)
Me.ChangesCancel.Visible = False

End Sub

Answer #17    Answered By: Daisy Phillips     Answered On: Nov 24

I am very grateful for your response, which I tried, but
unfortunately it still isn't working . . . I even tried asking for
the ListIndex values in a Msgbox and the values look to me like they
should work (i.e. if I clicked on a name down on the list, the
msgbox said that it was ListIndex 33 or whatever . . . but it still
didn't reset to that line, it still came back  to the listbox
scrolled to the top with nothing highlighted, like it has reset to
ListIndex = - 1 . . . I did try the 'repaint' and it just kicked
back an error on compiling and wouldn't run.

I don't know what I'm doing wrong. Apparently, I must have
something else in the code  interfering with that textbox  change
event, as it connects to the listbox  click event  and the 'loadrow'
sub which takes whatever is highlighted in the listbox and puts it
into the textboxes. I stepped through it using F8 line by line and
when I did that, it highlighted the correct line in the
listbox! . . . Go figure.
Why it won't work when I'm running it straight doesn't make
sense. I'm totally lost, because everything I've read says it
should work.

Maybe I've hit the "other direction" of the same problem that
Lisa was working with, so to speak. Maybe I need more guidance on
the debugging part of it . . . ? . . . or mayby I've written too
much code so it goes back over it and changes it back to listindex
= -1 . . . which is how the listbox is loaded when the form is
intialized. Or, any and all of the above in a bad combination.

How do I debug that to find out? . . . or what else might I do
to get it to work?

At this point, I'm ready to submit an application for "dumber
than a post" in case anybody needs one. Anybody know a position
that's open for that, let me know. I'm probably missing something
obvious, but heck . . . it's a good thing that life is full of

And I've tried to recode it so many times, I really think that
someone smarter than I am needs to give me a new start to work
from. If I posted my present code now, I don't even know what was
the original nor what was one of the suggested . . . all I know is
that none of them worked.
I do hope someone laughs about this . . . I could use a "humor
is the best medicine' kind of laugh.

Answer #18    Answered By: Keiko Mori     Answered On: Nov 24

Are you using the change event  of the worksheet? That could trigger after it
has set the listindex correctly.

Are you using AutoFilter, ShowAll, or ScrollArea. These can have weird reset
effects. I am struggling with them on my project at the moment and finding
variables get reset unexpectedly.

When you step through with F8 does the macro terminate so the VBE comes out of
break(debug) mode. In my project it was jumping back  in because of the change

Of course this may not help except to reassure you that you are not the only
Excel programmer with problems.

Answer #19    Answered By: Code Guru     Answered On: Nov 24

Thanks for your encouragement and ideas, and I think you could be
onto something, but I'm not sure. First, let me answer your

Answer #20    Answered By: Qadriyyah Malik     Answered On: Nov 24

I wonder about having to set up so much of the listbox  in the listbox click
event. It would be better to have the setup done once and leave it like that.
Surely you do not need to tell the listbox to have 4 columns and what their
widths are every time it is clicked?

Actually, I would be reluctant to use the click event  of a listbox at all. I
would prefer to use the change event if possible but this may not be
appropriate in your application.

Answer #21    Answered By: Terry Williams     Answered On: Nov 24

it worked, I
got a little overexcited. After your comment, I have no idea why
those 2 lines about the listbox  setup were still in the click event
code--except that I had copied part of this userform  code from an
old one a couple of years ago which was different enough for that to
not be a problem. I do hope I write tighter code  now, but I missed
that. I have noticed that many of the problems I've had came from
trying to do too much, and this was one of those.

The solution was simply to delete these 2 lines from the listbox
click event:
ListBox1.ColumnCount = 4
ListBox1.ColumnWidths = "160;50;20;0"

But, see? . . . I told you that I was missing something obvious.
I have no idea why it didn't occur to me to try that when
that line showed up as the place where the problem happened. Oh,
well. Live and learn.

In answer to your second comment, actually the click event  is rather
helpful instead of the change event for the listbox in this
particular case, but I certainly appreciate your question about that.

Didn't find what you were looking for? Find more on 2003 - Setfocus on userform Or get search suggestion and latest updates.