Tall drop-down lists (Office97)

Hi

With combo boxes, I prefer to make the drop-down list as long as possible, to minimise users having to use the vertical scroll bar. I achieve this with:
<pre> comboboxname.ListRows = comboboxname.ListCount</pre>

Re: Tall drop-down lists (Office97)

But seriously, consider using cascading combo boxes, for instance a first combo box to select a category, and a second combo box to select an item within that category. With two combo boxes with 15 items each, you already have 15*15 = 225 choices. Downside is that it is more complicated to populate the second combo box.

Re: Tall drop-down lists (Office97)

Is the list alphabetical so users can "type to match"? For people who prefer not to mouse, that's probably the most convenient. For those who prefer the mouse, a scroll bar isn't going to ruin their day. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Re: Tall drop-down lists (Office97)

Well, since it is a combo-box it is in a form, right? So you can always use Me.height. and calculate back from there.

I have a few applications that use dynamic controls, even move the command buttons around so that the "rebuild" button moves next to the item just chosen.

In your case you know the height of the user form (Me.Height), you know the top of the combo-box (Me.cmbBox.Top), so you know how much distance you have left on the form. You know the current font size (I use dynamic fonts) as, I think, Me.Font.Size. Form there it ought to be a simple calculation, with allowances for spacing between controls and so on.

Re: Tall drop-down lists (Office97)

But I want even more height than that, Chris.

Combo boxes with scroll bars are RSI-factories, and also highly inefficient. (Try counting the number of physical actions you have to make to select an item near the bottom of the list.) So I always aim to get every inch I can, to minimise the chance of there being a scroll bar. It's usually no problem, as most lists fit on the screen. But occasionally you'll get a particularly long one that you don't won't to subdivide. Say, a list of staff members.

The only possibilities I could think of are:
<UL><LI>Determine the height of the screen. (Can you do that?) But you'd have to also allow for items like the task bar. (A long drop-down list gets hidden "underneath" the task bar.)
<LI>Is there a property that forces a vertical scroll bar even if it's not theoretically needed?[/list]Thanks
Dale

Re: Tall drop-down lists (Office97)

What application are you building this in? Personally, I work with 32 rows, no more. that will always fit on an 800 x 600 display, won't bury part of itself off the screen, and is about as much as the human brain can deal with in a chunk. Having a long list may seem more efficient, but if you have matching turned on anyhow, what's the point? Does the user really scroll for values, or do they start typing and hope for a match? If they aren't familiar enough with the contents to try typing in a value, then somewhat shorter chunks will make it *easier* for them to find what they want because they'll break the list up into manageable groups.

Re: Tall drop-down lists (Office97)

The attached document is an attempt to dynamically set the number of items in a combo box depending on the screen parameters. As I say in the document, it's not perfect and I didn't have time to test it with every screen resolution but I hope that it will provide a starting point at least.

It was written usiing Word 2003 but doesn't use any 2003 special functions. I have tested it using Word 2000 and it works.

Re: Tall drop-down lists (Office97)

> But I want even more height than that, Chris.

I have to think a bit more. When i saw this I decided to do what you wanted - and found that I couldn't.

It's not the height of the combo box that is the problem - so ignore my previous comments - it's the height of the drop-down that appears when the user clicks on the combo. Late last night (a bad time!), my few attempts seemed to reveal that (Word97SR2) the height of the drop-down list was constant, and while it would extend beyond the lower boundary of the GUI form (a revelation to me!), I couldn't seem to extend it further than about eight or ten lines of data.

Maybe the modern versions of Word are better at this?

I do have a family of routines that negotiate with screen size, and have implemented them to adjust a GUI form to the user's screen specs (640, 800, 1024 etc), with a little success. I say a little, because the pixel measurement didn't ever seem to correspond to real life, so i always use a "fuzz-factor" of about 95% to come close to filling the screen.

You are welcome to the code if you'd like to examine it. I have to go back and look at the actual drop-down OF the combo-box.

Re: Tall drop-down lists (Office97)

Another approach might be to limit the number of items loaded in the combobox at any one time to some specified number that will fit on the screen w/o need for a scrollbar. If interested, see attached doc for a simple example. When UserForm is loaded, the combobox is loaded with first group of items:

The UserForm has 2 command buttons, Next & Previous, that first remove all items, then load the next or previous group of items as specified by intListItems. In above example this number is set to 26. When you get to first or last group (as specified by intMaxGroups) the appropriate button is disabled:

Private Sub CommandButton1_Click()

Dim i As Integer
intGroupIndex = intGroupIndex + 1

' Remove all items (index is zero based):
For i = Me.ComboBox1.ListCount - 1 To 0 Step -1
Me.ComboBox1.RemoveItem (i)
Next i

If intGroupIndex = intMaxGroups Then
Me.CommandButton1.Enabled = False
End If

If intGroupIndex > 1 Then
Me.CommandButton2.Enabled = True
End If

End Sub

The main issue would be to figure out how many total items there are, and how to break them up into groups. If using a data source to populate combobox, you could use a recordset to add items to list. This may or may not be practical.

There are two subs that can be called from doc, ShowUserFormModal & ShowUserFormNonModal (the latter would not work too good if using Office 97....) If using Office 2K or later, the non-modal UserForm will be positioned in upper left corner of screen so that all 26 items are clearly visible. If opened modally, the bottom of list when dropped down is not visible; I don't use UserForms that often so do not know of good way to position modal UserForm anywhere other than center of application window (in ShowUserFormModal sub, I tried using MoveWindow API function in place of UserForm Move method, but this did not work any better.... [img]/forums/images/smilies/sad.gif[/img] ...if anyone knows the secret for repositioning a modal UserForm, plz advise...

Re: Tall drop-down lists (Office97)

I'm glad it helped. When I first started programming, way back in the days of assembler when hobbits were still about, my mentor gave me a steel ruler. He said that every programmer needs a ruler. I still have that ruler and I still use it.

Just goes to show that things don't change that much.

The changed code works fine on my 21" monitor and an old 15" monitor we have attached to a server at various resolutions.