Userform Design Best Practices

Posted on by Dick Kusleika

Ross made a good comment on my Scrolling ListBoxes post about when to use a ListBox and when to use a ComboBox.

I started thinking about how I decide which to use, but haven’t had much luck defining my unified theory. At the simplest level, I think Comboboxes were intended to be a control that users could select from a list or type in their own text. ListBoxes, on the other hand, were just for selecting from a list.

That, of course, is too simplistic of a view. The fundamental difference in a appearance of these two controls probably has more to do with which one is used than anything else. Also, ComboBoxes have a MatchRequired property, which makes it the same as a listbox with respect to user-entered text. (I believe the name ComboBox comes from the fact that it is the combination of a TextBox and a ListBox. Requiring a match with the list destroys the TextBox part of the combination.)

I trolled around Excel to see what the pros use in different situations.

ComboBox ListBox Both
File>Print File>Open/Save As File>Properties>Custom
File>Page Setup Edit>Goto Edit>Move or Copy Sheet
Format>Conditional Formatting Insert>Name>Define  
Data>Sort Tools>Share Workbook  
  Tools>Macros  
  Tools>Add-ins  
  Data>Get External Data  
  Window>Unhide  

Okay, that doesn’t help much. I hoped to see some overriding characteristic that determined which control was most suitable, but it eludes me. Here’s my preliminary list of considerations:

  • MultiSelect capabilities – If you need multiselect, you pretty much have to go with a listbox.
  • Space considerations – Even if you have room for ListBox, it may not be the best choice. But if you don’t have room, that decides it.
  • View nearby entries – I think of this as keeping the user informed what his choice was (and wasn’t). The user selects a customer and another control populates with all that customer’s projects. I tend to go with ListBoxes in this scenario.
  • Emulation/Standardization – If I want a control with all the references in a VBAProject, I will probably choose a ListBox because that’s what Excel uses for the same task.
  • Natural or artificial key – Whenever the selected item is an artificial key, I use a ComboBox. For instance, I’ll use a ComboBox to select a customer ID which populates TextBoxes with name, address, etc. The ID isn’t the desired information, it’s just a means of getting to it, so I keep its display low profile.
  • Transfering elements – Whether moving entries in a control up or down, or moving entries between two controls, ListBoxes seem to be the way to go. It kind of relates to the nearby entries consideration. The user simply has to see that the entry is in its new location and not in its old location.

This isn’t my bible of control selection considerations. In fact, I just made it up. I thought about what situations made sense for which controls in projects that I’ve done and tried to codify why I made that “intuitive” choice when I did.

I’m sure graphic designers have some basic rules that they learn in GD101. I didn’t choose those classes as my electives and Calculus and Astronomy didn’t help much in this regard. It would be nice to see what considerations graphic designers make when laying stuff out, or if it’s a fuzzy process that takes some inherent creative talent. For my sake, I hope it’s not the latter.

What considerations, if any, do you make when you place controls on a userform?

Posted in Uncategorized

10 thoughts on “Userform Design Best Practices”

  1. Good summary, Dick. I don’t see what the author Ross referred to was getting at. Combos are fine if the user only needs to select one item, but I think listboxes are much better for multiple selections, and easier to read/browse. For example, I download movie times to a spreadsheet each week. I select the date using a combobox, but I select the movies in which I am interested using 2 listboxes (listbox A = unselected, listbox B = selected). Then I go and spend Saturday afternoon watching movies instead of playing with Excel.

  2. “what the author Ross referred to was getting at”

    I think his point was a real-estate one. I think that’s an important consideration, but only one consideration of many. Nevertheless it made me think, so I liked it.

    “spend Saturday afternoon watching movies instead of playing with Excel”

    Sounds like a darn good way to spend a Saturday. Now tell us how you are ridiculed by your non-tech friends for using Excel to select movies.

  3. I agree with what you have said Dick,

    ‘I was going to add this, but when i thought about it, it’s just the same ‘as what dick said – picking only one thing i.e not MultiSelect and space
    ‘ i’d add:
    ‘- speed, i seem to feel that a combo is quicker, i dont know why?
    ‘- if you use a small userform, like a popup, i use a combo, even if it’s ‘a long list – guess this is the space thing agian really.
    ‘- list of recent “strings”, agian i’d use a combo, but this might be ‘cus ‘your user is only going to pick one.

    GUI design is a big thing, just do a google, here might be some good links… could not find any thing that help on this post though.

    Useful GUI links? –
    http://www.blinn.net/hci-urls2.htm
    http://www.cems.uwe.ac.uk/~dfairbur/ KGD/isd2/GUI%20and%20Systems%20Development.ppt
    http://www.blinn.net/hci-urls2.htm

  4. Dick, speaking of designing, here is what somebody with intimate Microsoft knowledge writes about combo boxes:
    “Combo boxes are great for entering data into fields where only a limited range of values is likely. For example, entries in the title column of the ClubMembers table are likely to be mainly Ms, Miss, Mrs an Mr entries with a scattering of Dr, Prof and Rev entries and maybe some in different languages, like Mme or Herr. Users of your form can be presented with a list of the commonest titles for ease of selection and still have the option of typing in rarer ones.”
    (from Accessible Access 2000, by Mark Whitehorn and Bill Marklyn, Springer-Verlag)
    Bill Marklyn was Development Manager for the first two major releases of Access. He oversaw the entire design of the product. (In Access you can opt for the ‘limit to list’ property, which forces the user to use only the options that appear in the combo box; maybe it’s the same in Excel?)

  6. Frank, I’ve seen a web page which explianed “limit to list” in access, i dont think it is a method in Excel – i may be wrong, but i reckon you could code for it in combo using before update.

  8. FYI – In Excel, to restrict the selection to items already in the combobox, just set its style property to fmStyleDropDownList.

    From the help file (if that style is used): “The ComboBox behaves as a list box. The user must choose a value from the list.”

  9. Hi to All,

    My question will be about the resizing of the components such as ListBox, TextBox, and Date & Time Picker…

    Prior to asking my question, let me describe what wrong happens with the Excel sheet.

    I put everything in order: placing the D & T Pickers and ListBoxes in the approtiate places, setting up their width and sizes.

    After compiling and saving up the file, i close it. When I launch the Excel file in question, i see that sizes of these objects are changed?!?

    So, my question is that how i can prevent this auto-resizing thing? Setting height and width by VBA did not work…

    Thanks any way… Looking forward to hearing from you.

    Cheers,

    Ali

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.