Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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>

    Normally this works well.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Tall drop-down lists (Office97)

    Use fewer items in the combo box. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    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.

  4. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    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>

  5. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  6. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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

  7. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Charlotte

  8. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Tall drop-down lists (Office97)

    Hi Charlotte

    I'm usually work in VB for Word or VB for Excel.

    I agree that it's desirable to have short lists, but this isn't always practical or useful, and it's these situations I'm addressing.

  9. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tall drop-down lists (Office97)

    > Determine the height of the screen. (Can you do that?)

    Determining the width and height of the screen in pixels, using VBA (Word):
    System.HorizontalResolution
    System.VerticalResolution

    Jan

  10. #9
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    I hope this helps.

    Regards,

    Kevin Bell

  11. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  12. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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:

    Option Explicit

    Dim intGroupIndex As Integer
    Dim intMaxGroups As Integer
    Dim intListItems As Integer

    Private Sub UserForm_Initialize()

    Dim i As Integer
    Dim n As Long

    ' Initialize variables:
    intGroupIndex = 1
    intMaxGroups = 10
    intListItems = 26

    Me.CommandButton1.Caption = "Next " & intListItems & " >>"
    Me.CommandButton2.Caption = "<< Previous " & intListItems

    ' Add 26 items to combobox:
    For i = 1 To intListItems
    Me.ComboBox1.AddItem "Item " & String$(intGroupIndex, Chr$(i + 64))
    Next i

    With Me.ComboBox1
    .ListRows = intListItems
    End With

    End Sub

    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

    ' Add 26 items to combobox:
    For i = 1 To intListItems
    Me.ComboBox1.AddItem "Item " & String$(intGroupIndex, Chr$(i + 64))
    Next i

    With Me.ComboBox1
    .SetFocus
    .DropDown
    End With

    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...

    HTH

  13. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Tall drop-down lists (Office97)

    Thanks, Kevin.

  14. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tall drop-down lists (Office97)

    > actually got a ruler out and discovered that the measurements for the task bar and for the screen size seem to be in different units

    My findings, too. And like you, my "fudge" worked at some resolutions better than at others.

  15. #14
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Regards,

    Kevin Bell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •