How to reference Controls in a Userform based on the control's tabindex instead of name?

[bmxfreedom]

I have a potentially unique situation... I have a userform with a multipage that contains 20 comboboxes which all reference the same list of items. The rowsource for each of these comboboxes is a list of search options which each have 1 of 3 different logical dependencies (the search text contains, vs the search value is between value1 & value2, vs the search is logical; a true/false option.)

To the side of each of these comboboxes are a set of textboxes, checkboxes, and labels stacked on top of eachother which are preset to .visible = false. When the value in the combox is changed, its Before_Update event triggers to set the appropriate textbox(s), checkbox(s), and/or label(s) to .visible = true based on the appropriate logical dependency for the selected search option. All but the first of these 20 comboboxes have .visible preset to false as well, and then when the first textbox or checbox changes (again via the before_update even for each), the next combobox is set to .visible = true so that another search option can be picked from the list.

It is set up this way because the list of search options is overwhelming if they are all visible at the same time. While it is highly unlikely that a user will ever need more than 10 or so of the available options at once, the number of available options exceed 180, so having them all on the form visible is simply not an option...

The problem I'm having is that when .visible = true is set for the appropriate textboxes, checkboxes, and labels, I can't for the life of me get the focus to set on the appropriate control. The after_Update even doesn't work either. The change event would work, except that I want the users to be able to scroll using up and down arrows through the list of search options in each combobox, so the change event would trigger with each new selection and exit the combobox prematurely... Obviously, I can't have that, so...

My idea is that in the multipage change event, I could specify that when on this page (page 6 in this case), the event could identify the active control's tabindex and specifically select the next control based on that tabindex. (something like activecontrol.tabindex + 1), but I don't know how or if there is a way to do this.

Any help is much appreciated!

For your review, the following is part of the code I have for a single combobox and its associated labels, textboxes, and checkboxes. Aside from setting focus appropriately, the code functions as I had hoped.

The control names might be a tad confusing, so here's an explanation:
"SearchOption1" is the first combobox.
"SO1Contains" is a textbox for searching for text containing said value
"SO1Between1" and "SO1Between2" are textboxes for values to search between
"SO1Between3" is a label that goes between them ("and")
"SO1CB" is a true/false checkbox for searching where a value is true or false
"LblS2" is the label for 2nd combobox, titled "SearchOption2", both of which are made visible by changes to the above textboxes, and checbox.

Please Login or Register  to view this content.

[bmxfreedom]

The following is what I have written so far in the multipage change event. It's not much, but maybe it will help someone understand better what I'm trying to accomplish...
Any help is much appreciated!

Please Login or Register  to view this content.

[bmxfreedom]

Now that I'm looking at the MP1_Change event code, I'm realizing that it would probably have the same effect as using the change event in each combobox...
Any ideas would be greatly appreciated.

[bmxfreedom]

I found a sketchy workaround using sendkeys...
In the exit event for each combox, i will use:

Please Login or Register  to view this content.

This causes the focus to set on the correct control. I just hate using sendkeys if I can avoid it; so many problems with that method...

That being said, if anyone has any other ideas, please let me know. Many thanks in advance!

[Norie]

Any chance of a sample workbook?

PS Have you looked at using the Tag property and/or classes?

[bmxfreedom]

Dang! It fails on the following line in and after SearchOption2_Exit:

Please Login or Register  to view this content.

I don't understand why.

To shed a little light on what this line is doing; the value in each of the comboboxes SearchOptions 1-20 all start with a number and a ":", like "123: " then the description of the search option and logical test. So, the final value ends up looking like this: "38: Product Arrival Date, Between", and this line of codes extracts that much needed search option number (OptNum) of 38 in this case... It works flawlessly in the rest of the code. Why the failure now?

[bmxfreedom]

Any chance of a sample workbook?

PS Have you looked at using the Tag property and/or classes?

Unfortunately the sample workbook is very large and contains private customer and employee data. This is a special order system that I'm working on. It has turned out to be much more complicated than I originally though...

I'm not sure what you mean my the Tag property and/or classes. Can you explain?
Thanks.

[Norie]

We don't need to see the actual workbook.

Just a workbook that reflects it without any sensitive data.

Basically something that gives us an idea of what you are trying to do.

[bmxfreedom]

Thanks for the quick replies. I'll see if I can't put something together... Problem is, there are a lot of dependencies on multiple cell values. I'm not sure I can get into a small enough file... I'll give it a shot though.
Thanks.

[bmxfreedom]

Okay, I've attached a bare-essentials version that represents the part I'm currently working on; just the search. It is nowhere near done or pretty... Please have a look at it if you'd like. Thanks in advance for any tips you can provide!

[bmxfreedom]

I'm just starting to figuring out what the Tag property is and how it can be used, but I don't yet see how it would benefit this situation. Any suggestions?
Thanks.

[mikerickson]

Perhaps this will help.

Please Login or Register  to view this content.

[bmxfreedom]

I think I may have just figured out a way to make this do what I want... I need to set up a change event for each combobox that does what my before_update events are doing, and then in the before_update events, I need to simply do the date and value format checks and the .setfoucs there instead. I believe what's causing the problem is that the items I'm trying to setfocus on are not visible during the event, even though the event makes them visible; it's like they don't become visible until after the entire event has run. If the change event triggers the visibility, it will happen before the before_update event, and that event can then set the appropriate focus. Going to work on this now...

[bmxfreedom]

Perhaps this will help.

Looks intriguing... I'll give it a shot. Thanks! If it does work, it will be a lot more simple than the massive amount of code that the 'solution' in my previous post would require. As it turns out, I would need to set up change vs before_update events for every control on the page. Hopefully this method saves me that pain...

[bmxfreedom]

Sorry mikerickson, your code doesn't have an effect. At first I thought it was because of forgot to change MultiPage1 to MP1 to match my userform, but after doing that it still had no effect. Shouldn't it be giving me a message every time I change something?

[bmxfreedom]

Well as far as I can tell, the fix was just to place .setfoucs in the before_update event and .visible in the change event for each of the 20 comboboxes. I would love a more elegant solution if anyone has any. In the meantime, here's what I've come up with; duplicated per comboxbox, textbox, checkbox, & label set. It's a lot of code (80,000 more words than this thing will let me post...), but it works...

Please Login or Register  to view this content.

[mikerickson]

Sorry mikerickson, your code doesn't have an effect. At first I thought it was because of forgot to change MultiPage1 to MP1 to match my userform, but after doing that it still had no effect. Shouldn't it be giving me a message every time I change something?

What that code should have done is give you a message every time that you click on the body of the userform. This is a better form. This assumes uses CommandButton1 as a demo of the function NextMultiPageControl. Note that the multipage is passed to the function as an argument.

Please Login or Register  to view this content.

In the OP, you said that you want the next in tab order control after the active control. And this function does that. I don't understand what you are doing with that, so I can't advise how to use it for your situation.

One possible refinement of the UDF would be to pass an optional argument for page number, that defaults to the Active page. But, as I don't understand what the UDF is to be used for, I couldn't advise if that would be a useful feature or an annoying bell/whistle.

[bmxfreedom]

In the OP, you said that you want the next in tab order control after the active control

Thanks for all the help. mikerickson, your answer is a perfectly applicable response to my very inapplicable question... What I was trying to do was way off base from what I needed to do. The eventual solution was based in a combination of control change and before_update events, and thus, selecting the next control in the tab index order actually would have failed on some occasions because the next control in line wasn't always visible. It actually needed to be name based, and since I renamed all of the controls with names that could be referred to by each event based on the name of the active control, I could have written a set of macros that could be run each time a change or before_update event happened in each control. Before realizing this potential, I had already written all of the code necessary to make it happen anyway... Thanks for all your help. I have marked this post as solved.