View Full Version : Dynamically show and hide optional controls in a userform.

2011-05-04, 01:00 AM
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:

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)
A procedure that replaces the 'Choose from dropdown' text with the appropriate list items when a user clicks on the dropdown arrow.
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.


Jan Karel Pieterse
2011-05-04, 08:21 AM
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
2011-05-04, 10:07 AM
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.

2011-05-04, 10:18 AM
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.

2011-05-04, 10:34 AM
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
2011-05-04, 10:43 AM
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
2011-05-04, 10:57 AM
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.

2011-05-04, 12:44 PM
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
2011-05-04, 12:47 PM
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.

2011-05-04, 01:03 PM
Thanks Jan Karel.

2011-05-04, 01:16 PM
Forgot to mention that another reason I was looking for a non-cell approach is that I wanted users to be able to type multiple lines as input..for instance in the 'Brief Overview' field. Excel cells would require you to use ALT-ENTER to enter a new line within the cell, this, which users may not know. Or they might know, but (like me) not remember until they've pushed ENTER.

2011-05-25, 01:28 AM
I managed to overcome the tabindex problem using the ActiveX-based form, but it took a heck of a lot of code, and I get all sorts of horrible flashing despite the fact that I've turned screenupdating off during the code application. Plus the form is sometimes increadibly slow in Excel 2010, which we are moving to soon (currently using 2002.)

So I recreated the form using a worksheet-based approach as suggested above . Managed to escape the grid by making column cells very narrow and then merging particular blocks of cells together to get different sized input blocks.

The only problem is that this stuffs up the tabbing. Here's what's going on:
1. Everything works fine until I hit a merged block of cells with the range M22:AJ27 ("Address")
2. When I push tab, then I get taken to the next input box which happens to be one row deep in the range AT22:BF22 ("Phone"), which is fine
2. When I push tab again, then I get taken to the next input box which also happens to be one row deep, in the range BL22:BR22 ("Ext")
3. But when I push the tab this time, I get taken back to the top of that multi-row merged block in the range M22:AJ27 again ("Address")

So I go round in circles.

I had a little experiment with Application.OnKey in the hope that I could get it to go past the "Address" box any time that Tab was pushed from the "Ext" box, but couldn't work out how to still allow tab to act like tab in the cases that it is anywhere else. That is, how the heck do I get excel to tab with VBA?

Ken Puls
2011-05-25, 07:41 AM
Hi Jeff,

I haven't actually looked at your file, but based off your description... can you work with the Worksheet_SelectionChange event? Rather than trying to use onKey to capture the key as it's pressed, use the SelectionChange to react to where you end up and move the user to where you want them?

2011-05-25, 10:34 AM
Of course! Why the heck didn't I think of that? Your comment has also helped me realize that tabbing is only part of the picture anyway...I also need to make the next cell to the right be selected on enter ( Application.MoveAfterReturnDirection = xlToRight ) rather than down (the default movement)

Thanks Ken.

Ken Puls
2011-05-26, 12:02 AM
LOL! Sometimes we get so focussed we lose site of other routes to the same goal. Been there many times myself. <grin>

2011-05-30, 12:33 PM
Bob...not sure if you are still monitoring this thread, but wanted to say that I just found your dynamic dependent dropdowns example at
http://www.xldynamic.com/source/xld.Dropdowns.html which is a real time-saver for my now workbook-based approach. THe data validation example is just what the doctor ordered. Tomorrow I've got to make a small change so that if there is only one possible choice in the 2nd dropdown then that choice is selected automatically. But this has saved me significant time. Next time you're in Wellington New Zealand I owe you one (if not several) of our fine Tuatara Brewery beers as recompense.


Bob Phillips
2011-05-30, 12:57 PM

You should be careful what you promise. I am coming to New Zealand in October. It is primarily to visit my daughter, and tour the South Island, but I hope to travel up to Auckland in November by train, which would entail stopping over in Wellington for a night.

2011-05-30, 11:38 PM
Awesome...New Zealand in desperate need of hard currency! Looks like you're taking a good amount of time to get around...fantastic. I look forward to shouting you those beers, and showing you round if you need it.

Here's two of my favourite places in the South Island:

Make sure you include Wharariki Beach in Golden Bay (Right at the North West tip of the South Island)...it is one of the most beautiful places in New Zealand that I've been. Lovely limestone islands with archways big enough to fly a small plane through, awesome caves, amazing wind sculpted sand, and a seal colony to boot. Plus hardly anyone there. Check out http://www.goldenbaynz.co.nz/vg_wharariki.html Another awesome place easy to overlook is the Catlins, which is on the South East coast. There's a great tidy little campground with comfy cabins we stayed at in a place called Surat Bay, plus a clean backpackers right next door if that's all you need. You'll have sealions and penguins galore on the beach right outside (in fact the sealions often sneak up behind you). Check out http://www.visit-newzealand.co.nz/catlins/newhavenholidaypark.html or http://www.newhavenholiday.com/index.html (note it is much more interesting than it looks on these sites). Also in the Catlins is the stunning view from Nugget Point lighthouse (take a bottle of bubbly, some nice cheese and crackers, and go at dusk), and many other great nooks and crannys.

I can also recommend some amazing places to visit in Northland (north of Auckland) where I come from originally. Please let me know if you decide to poke your nose up there, and I'll give you a suggested itineary. You won't regret visiting there. I'm at weir dot jeff at gmail dot com.


2011-05-31, 04:55 AM
Bob: just noticed a strange behavior in excel 2002 (which is what they give me at work) with your dropdowns code...running it seems to trigger 198 refreshes. If i type =rand() into a cell, then activate the first dropdown, then I see the results of the RAND function cycle though those 198 refreshes. And if I add the line debug.print "test" just before END SUB of Private Sub Dropdown_Change(ByVal Target As Range) then I see that "test" gets printed in the immediate window 198 times.

Any idea what is causing this, and how I might circumvent it? I attach the workbook.