Dynamically show and hide optional controls in a userform.

[JeffreyWeir]

Hi all. I've emulated a paper form in and Excel worksheet using some activex controls. Note this is NOT (currently) a userform...it sits on the worksheet. This is attached. There's a few macros that work behind the scenes, including:

1. A procedure to put default 'helper' text in gray in some of the fields, and to select this text if a user clicks in the text boxes(so they can then delete it in one go if they choose to, rather than having to select all the helper text themselves)
2. A procedure that replaces the 'Choose from dropdown' text with the appropriate list items when a user clicks on the dropdown arrow.
3. A procedure that shows or hides sections of the forms depending on what is selected in the 'purchase type' dropdown i.e. if a user selects 'Asset purchase only' then the capex fields are hidden, and vice versa for opex.

A problems with this approach is that there is no TabIndex functionality for ActiveX textboxes unless they are sitting within a userform. So I can either write some code that shifts the user through the text boxes based on the control name, or recreate the form in a userform and use the TabIndex functionality that this enables.

But if I use a UserForm, i need some sections of the UserForm to be hidden or unhidden depending on the options they select.

Does anyone have any examples of such a form they could post or point me to?

It would probably be better to NOT use userforms - and instead write some code that directs the taborder of the existing form. But then again, I could do with some exposure to programming a UserForm...I haven't played around with them much.

Thanks for any help.

Jeff

[Jan Karel Pieterse]

Not an answer to your question per se, but I do have a remark on the form.

I'd be irritated if I would have to fill in this form.
Why? Because there seems to be no way to stay with the keyboard to work your way through the form. After each box you have to grab the mouse to go to the next one. On a userform you could use the tab key, in normal cells you could just use the arrow keys (or if the sheet is protected, the tab key). I would have sticked with Data Validation to create the input fields, rather than ActiveX controls.

[Bob Phillips]

I agree with Jan Karel, I would avoid ActiveX controls on a worksheet like the plague.

The form has a very nice visual appeal (to me), something that I think you would struggle to achieve in a form (even a blue form would feel odd to me), so I like the idea of using the worksheet, and Excel has some singularly powerful worksheet functions such as DV, so I would just redo it.

You can use shaded borders to make the cells look like textboxes. As for tabbing, for a normal w/s, take a look at http://www.xldynamic.com/source/xld.xlFAQ0008.html.

[JeffreyWeir]

I'd be irritated if I would have to fill in this form..[because] there seems to be no way to stay with the keyboard to work your way through the form. .

Yes, precisely, which is the point of my post above, where I stated that the problem with the form at present is that there is no TabIndex functionality, meaning I either have to write some code that shifts the user through the text boxes based on the control name, or recreate the form in a userform and use the TabIndex functionality that this enables.

Today I spent some time writing a dynamic routine that puts the name of each Textbox or ComboBox into an array along with the .top and .left position, then sorts that array by first .top and then .left so that the list of controls is in the same order as on the form. The intent is to use this to emulate some kind of TabIndex functionality...so that if a user has activated a particular control, then when they push the Tab key the next applicable inputbox is selected.

[JeffreyWeir]

I agree with Jan Karel, I would avoid ActiveX controls on a worksheet like the plague.

Thanks Bob. It would be good to know your particular reasons for avoiding ActiveX controls. I haven't (yet) had bad experiences with using them. (Initially I was trying to use form controls, but found them too limiting and so switched to ActiveX) But then, I'm fairly new to Excel. So I appreciate any specific advice on the pitfall of using Activex.

I like the idea of using the worksheet.

The only problem I have with using the worksheet is that I hate being tied to a grid, and think I can make much better use of space if I can escape the grid, plus make the form a bit more visually appealing and emulate the other forms our dept uses. If it looks official, people are more likely to fill it out.

Thanks for the tabbing link...I'd forgotten about that.

[Bob Phillips]

I agree form controls are more limited, but have had all manner of problems with ActiveX controls, it is so long since I used them I have consigned the memories to the dark recesses. I know they have corrupted, moved, resized, all sorts. Actually, I am not an advocate of any controls on a worksheet.

[Bob Phillips]

I like the grid, because it gives you all of the Excel functionality. Forms are too much work.

I just knocked up a part of the form using no ActiveX controls and shading the cells. I would put DV in the cells to control the input and that tabbing technique, and hey presto.

[JeffreyWeir]

Ha, you're a purist through and through, Bob!

I certainly won't argue that forms are much more work...I had to find out about how class modules work just so I could use a generic procedure to handle events, rather than a whole bunch of separate routines for each control. And my VBA skills are still very much intermediate. However, working out how do to this - and whether I could get ActiveX controls to do the other things I wanted over the last few days - certainly has increased those VBA skills.

Thanks for you input...I may yet revert to 'the grid' if I find I have issues. I don't think tabbing will be one of those issues, given my routine to select the next input box either to the right or down the form seems to be working just fine. One issue might be that someone less familiar with VBA might be apprehensive to make changes in future given my 'code heavy' approach, compared to if the form just used more rudimentary worksheet functionality. Mind you, dependent validation in a worksheet can almost as easily be mangled by a developer not knowing what they are doing. So I'll be sure to document whatever I do either way.

One reason I went with ActiveX controls in the first place is that I could quickly mock up something, and very easily drag, drop, and resize the input boxes independent to other input boxes in response to the very sketchy user requirements I had. Programming issues aside, I think the only way to have that independent level of control in the worksheet would be to merge cells where I needed to make something a specific size without affecting something else. But merged cells obviously bring their own problems.

[Jan Karel Pieterse]

ActiveX controls cause a lot of problems, they are susceptive to problems when e.g. using different Excel versions. Especially Excel 2010 seems to be picky when opening files from older excel versions which contain ActiveX controls.
If you set up the form to just use cells rather than controls, make sure you unlock the data entry cells and protect the sheet, disallowing selecting of protected cells. That way, hitting enter will cycle the cursor through the form. Also the arrow keys will be restricted to data entry cells.

[JeffreyWeir]

Thanks Jan Karel.