? set focus to a textbox when userform opens

[madhg]

I'm creating a userform for editing membership records of a small organisation. So I want the focus to default to textbox tboxFind which is used for searching the data.

I've put tboxFind first in the tab order, and in subroutine Userform_Initialize, the last line is tboxFind.SetFocus

Using Excel2000 on Windows Vista, it works OK.

Opening the same workbook in Excel2000 on WinXP, no textbox has the focus when the form opens, but once the user has started to use the form, the focus defaults back to tboxFind, as it should.

So why is the focus not where it should be when the form opens in WinXP?

[Leith Ross]

Hello madhg,

You have to remember that Windows Vista is the first Microsoft operating system to break from the past methodology of backward compatibility. If you need the code to work on previous systems then you should develop the code using the correct Windows version, if possible.

The UserForm Initialize and Activate events are different. The Initialize event happens only once when the UserForm is being created in memory as an object. The Activate event fires when the UserForm is actually displayed. You can't set focus to window or one of its controls, until it is displayed. Set the focus to your text box in the Activate event.

Please Login or Register  to view this content.

[madhg]

Thanks, Leith, that's very useful. I should have looked up Userform properties, methods and events in ExcelVBA Help!

However, I can't get it to work. I put the subroutine you suggested into my code, with a Debug.Print command to check that the subroutine was being executed. The subroutine does run (after changing Userform1 to Userform, which is strange since the name of the form is Userform1). But on XP, which is where the workbook has been developed, it does not set focus to tboxFind. When I put a different textbox into subroutine Userform_Activate, it sets the focus to that OK. But not to tboxFind.

But the other occurences of textboxFind.SetFocus in my code do set the focus whenever the user navigates to another record. The code is unfortunately too long and complicated for me to upload and expect anyone else to look at. I suppose I could try to concoct a heavily cut-down version that demonstrates the same problem, but that would take quite some time.

Anyway, at least I've learnt about the activate event. Thanks again,
David

[Leith Ross]

Hello David,

If you post your workbook, I'll take a look to see where the problem is.

[madhg]

OK, it's 1.2Mb, too big to upload, but I've put it where you can download it:

http://www.zen22109.zen.co.uk/madhg.xls

Many thanks!
David

[Leith Ross]

Hello David,

You are setting the focus to tboxLastName and not to tboxFind.

Please Login or Register  to view this content.

Change it to tboxFind and it will work.

[madhg]

Oh yes - that is because I had earlier today changed tboxFind to tboxLastName, to see whether I could set focus to a different textbox (yes, I could), and forgot to change it back again before uploading it for you to look at.

Right now I'm using a laptop with Vista, and don't have access to my usual XP machine, so I can't test it again - but I tried it out with tboxFind.SetFocus earlier today, on my XP machine, and it didn't set focus on startup. If it works OK for you on XP with Excel2000, then maybe there's something strange about the setup of my desktop machine.

with thanks,
David

[Leith Ross]

Hello David,

I ran it under both Excel 2000 and 2003. Both worked fine. Perhaps the 2003 copy you have has become corrupt. Delete it and reload it from a know good copy. It should work with no problem. Let me know what happens.

[madhg]

Well, I uninstalled and reinstalled MS Office 2000 on my XP desktop, from the same disk that I used last week to install it on my laptop running Vista. Still the same: no focus on startup on my desktop, focus OK on startup on my laptop.

Leith, I appreciate the time you've spent on this, and I've learnt something from it, but I don't think it's worth putting any more effort into it. It's not a major disaster if the user has to click in the textbox.

thanks and best wishes,
David

[romperstomper]

As a matter of interest, does it work if you set focus to another textbox and then set focus to the one you want?

[madhg]

Yes! Great idea! (Why didn't I think of that myself??)

Thanks very much,
David

[romperstomper]

Why didn't I think of that myself??

Because you're too close to it! Been there so many times...