Tall drop-down lists (Office97)

Topic

New Reply

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:

        comboboxname.ListRows = comboboxname.ListCount

Normally this works well.

Reply | Quote

Replies

Use fewer items in the combo box.

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.

Reply | Quote

Use fewer items in the combo box.

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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:

• 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.)
• Is there a property that forces a vertical scroll bar even if it’s not theoretically needed?[/list]Thanks
  Dale

Reply | Quote

> 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

Reply | Quote

> 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

• 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.)
• Is there a property that forces a vertical scroll bar even if it’s not theoretically needed?[/list]Thanks
  Dale

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 = “< 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…. …if anyone knows the secret for repositioning a modal UserForm, plz advise…

HTH

Reply | Quote

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 = “< 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…. …if anyone knows the secret for repositioning a modal UserForm, plz advise…

HTH

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks, Kevin.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks, Kevin.

Reply | Quote

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

Reply | Quote