Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    limit to listbox (E2000)

    Morning,

    I have a lisbox that will list the first 168 rows of my worksheet and then no more beyond that, I am currently on row 362 of what will be about 1500, is there a limit to how many rows you can have? (in the cell that populates the listbox I have set it to read 2000 rows)

    When the worksheet is eventually finished I think it will be cumbersome to scroll down the listbox to find the records that are required, is it possible to make a listbox interactive, i.e. if I type in B Belguim would show and if a carried on typing BER Bermuda would show.

    Thank you


    Stephen

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

    Re: limit to listbox (E2000)

    A list box can hold 65,535 items, as far as I know, but it'll cease to be manageable long before it reaches that limit.

    You can use a list box (or combo box) from the Control Toolbox and set its MatchEntry property to 1 - fmMatchEntryComplete. That will enable the user to locate an entry by successively typing letters.

    You could also used linked list boxes or combo boxes. For example, the first one to select the initial letter (A - Z), the second one to select an entry beginning with that letter. See <post#=384622>post 384622</post#> for an example with combo boxes from the Forms toolbar.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: limit to listbox (E2000)

    I'm sorry Hans and please excuse my ignorance but I already have the list box on the form, somebody helped me with it because I have never used list boxes in Excel. How do I get into the properties of the list box to check what you have advised me, I have clicked on the design tool, right clicked the list box and double clicked the list box (which brings up the VB pane).

    Thank you

    Stephen

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: limit to listbox (E2000)

    Press <f4> in VB to see the properties window. The select the item and you can view the properties of what you select on the userform

    If it is not in a userform, but an object on a sheet, then right click it a select properties.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: limit to listbox (E2000)

    The VB pane I opened previousely was actually the sheets VB pane and not the list box.

    If I right click the listbox I get a number of options (the same as you get for a normal cell) but with no properties options, if I double click in design mode nothing is happening, I have taken the liberty of attaching an eample if you could please be so kind to see what I am doing wrong, Thank you. (this particular sheet only needs to read 92 rows but it was a lot smaller than the other workbook that I am working on)

    Steve

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

    Re: limit to listbox (E2000)

    Sorry, I don't see any list box in the workbook you posted. Could you explain where you want it and what exactly it should do?

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: limit to listbox (E2000)

    The list box should be in B16 which lists airport destinations, F41 shows =VLOOKUP("*"&$B$16&"*",$A$43:$Y$92,25,FALSE) which shows the final calculation, on my other workbook I have changed $Y$92 to $Y$2000

    Hope that makes more sense

    EDIT: Sorry, I looked at the attachment and indeed the listbox is not there but it is on my desktop version so I will try to attach it again.


    Stephen

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

    Re: limit to listbox (E2000)

    When posting a question, it helps if you provide accurate information. It turns out that you were talking about Data | Validation with a dropdown list, not about a list box at all. When you wrote "I have a list box", we thought you had created a list box from the Forms or Control Toolbox toolbar. You didn't mention Validation anywhere, so we have been discussing at cross purposes.

    I have attached a modified version of the spreadsheet, with a combo box from the Control Toolbox positioned over cell B16. If you want to look at its properties, activate the Control Toolbox and click the first button to turn on design mode. You can then right-click the combo box, select Properties.

  9. #9
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: limit to listbox (E2000)

    Thank you and my apologies, I did not know that it was a validation thing. I asked for help in maaking a list box for the worksheet and that is what I was given. I shall try to be more specific in future, thanks again

    Stephen

Posting Permissions

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