Listbox forms control (2002/SP3)

Topic

New Reply

Is there a way to use the Up/Down keys to scroll through the list in a Listbox control (created from the forms control toolbar)?

Reply | Quote

Replies

I don’t think so. A list box from the Forms toolbar doesn’t receive the focus when you click on it – using the arrow keys, PgUp and PgDn etc. just move the active cell.

You *can* use the arrow keys in a list box from the Control Toolbox.

Reply | Quote

I thought so. Thanks anyways Hans.

Reply | Quote

You could use the .OnKey method

Sub AssignUp_DownKeys()
    Application.OnKey "{down}", "IncrimentFormsListBoxSelection"
    Application.OnKey "{up}", "DecrimentFormsListBoxSelection"
End Sub

Sub IncrimentFormsListBoxSelection()
    With Sheets("Sheet1").Shapes("List Box 1").ControlFormat
        .ListIndex = ((.ListIndex) Mod .ListCount) + 1
    End With
End Sub

Sub DecrimentFormsListboxSelection()
    With Sheets("Sheet1").Shapes("List Box 1").ControlFormat
        .ListIndex = IIf(.ListIndex = 1, .ListCount, .ListIndex - 1)
    End With
End Sub

Sub Up_DownToNorma()
    Application.OnKey "{down}"
    Application.OnKey "{up}"
End Sub

Reply | Quote

Thanks Mike. Can I use this code in the project’s (rather than the worksheet’s) module? And what’s the purpose of the sub “Up_DownToNorma”?

Reply | Quote

UpDownToNorma is missing an L. It restores the up/down arrows to their normal functioning

That code would go in a normal code module, not a sheet’s code module. You would call AssignUp_DownKeys whenever you wanted the arrows to effect the listbox selection and UpDownToNorma when you want them to have their normal function.

Reply | Quote

I’ll give it a try. Thanks very much Mike.

Reply | Quote

Mike,

I assigned the macro “AssignUp_DownKeys” to my Listbox control. When the macro is being called I get an error in either “IncrimentFormsListBoxSelection” or in “DecrimentFormsListboxSelection” subs, depending if I press the Up or Down keys. The error occurs at line 3 of each sub:
.ListIndex = ((.ListIndex) Mod .ListCount) + 1
.ListIndex = IIf(.ListIndex = 1, .ListCount, .ListIndex – 1)

The error is Run-time error ‘1004’: Unable to get the ListIndex property of the ListBox class. Any idea why it doesn’t work?

Also, does it matter if the Listbox is of ‘single’ or ‘multi’ selection type?

Reply | Quote

If you want the user to be able to use the arrow keys, you should use the list box from the Control Toolbox, not the one from the Forms toolbar. Trying to hijack the arrow keys it too tricky.

Reply | Quote

I thought about using a list box from the Control Toolbox, but the problem is that the listbox has to be of “multi-select” type and from my understanding, in order to set this up with VBA, you have to have the code in the worksheet’s module. This is a problem for me because in my code I’m adding a listbox to a newly created worksheet.

Reply | Quote

If you want to stay with list boxes from the Forms toolbar, I think you’ll have to live without using the arrow keys in the list box.

Reply | Quote