Userform Tab Order - automatic sorting 1

[DrBowes]

Has anyone written a procedure to automatically sort the tab order on a userform (i.e. starting with the top left control and ordering them across then down.

This would be useful to run on every userform in a project when you have finished with it to ensure the user doesn't end up tabbing all over the place. It can be quite time consuming to do this manually using the Tab Order command on the view menu.

If I planned better I could create the controls in the right order but I am too haphazard for that.

Cheers
Dr Bowes

 

[xlbo]

I havn't got a routine and it would depen whether you want to go all the way down then right and down or start from top left, go right till the end, then down one and repeat. Either way, you can loop thru the controls with

For each ctrl in me.controls
'do stuff
next

The properties that you'll be wanting are

ctrl.top and ctrl.left

which when viewed relatively, should enable you to determine where the tab order should be

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[TonyJollans]

Hi DrBowes,

I haven't got a routine either and, if no-one comes up with one, I might look at it because it intrigues me and I do think it might be useful. It would, however, be pretty complex when you considered all the possibilities with tabstrips and multi-pages, etc. A further complication in making a general purpose routine is that it really ought to work with non-loaded forms - Geoff's idea of using Me as a reference means (a) that the driver code must be within the form and (b) the form must be loaded although running the code would cause that to happen.

Don't expect anything quickly from me!!

Enjoy,
Tony

 

[xlbo]

aw c'mon Tony - just change Me to Formname and the premis should still work..... ;-)
You are right about tabstrips and multipages etc but I don't reckon it'd be too hard to do this for a "standard" form ie 1 page with a definite work flow.....

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[DrBowes]

Have since discovered can use for example Userform1.SetDefaultTabOrder on a Userform.

This sorts from left to right, top to bottom.

Sorry to have troubled.

Dr B.

 

[TonyJollans]

Sorry, Geoff, left planet for a while

What I was thinking about was the fact that the Userforms collection contains only loaded userforms so that unless you knew the name of the form you would have to run through the VBCOmponents collection, and the original question suggested doing it on all forms in a project.

Seems like Excel has it anyway (as so often - I wish I knew it all).

Enjoy,
Tony

 

[xlbo]

DrBowes gets a star for answering his own question and showing me something I didn't know about

Tony - I see what you mean - I was thinking about a single loop bit of code that could be run on each userform in turn rather than a double loop for looping thru userforms in a project and setting the tab order for all of them in an interior loop - only because a lot of userforms I create have a "non standard" tab order with objects appearing / disappearing dependant on selections etc for which I would like to manually set the order. In other words, I'd want a choice as to which forms get a "default" order and which are manual

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244