Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox forms control (2002/SP3)

    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)?

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

    Re: Listbox forms control (2002/SP3)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox forms control (2002/SP3)

    I thought so. Thanks anyways Hans.

  4. #4
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox forms control (2002/SP3)

    You could use the .OnKey method

    <pre>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</pre>


  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox forms control (2002/SP3)

    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"?

  6. #6
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox forms control (2002/SP3)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox forms control (2002/SP3)

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

  8. #8
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox forms control (2002/SP3)

    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?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox forms control (2002/SP3)

    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.

  10. #10
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox forms control (2002/SP3)

    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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox forms control (2002/SP3)

    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.

Posting Permissions

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