Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    ListBox Lunacy (XL 2003 SP2)

    Am trying to preselect an item in a listbox and it's driving me crazy. Particularly because it doesn't seem to behave consistently. The listbox has just one column and is not bound to an Excel range.
    Initially I tried lstBox.Value = "<name of 3rd item>", then lstBox.ListIndex = 2, and finally lstBox.Selected(2) = True. None of them consistently work. I should say that the inconsistency may be triggered by my stepping through the code. The .ListIndex and .Selected(2) properties DO retain their assigned values, but the .Value property is blank. And when the userform is displayed, the listbox has nothing selected (usually!).
    It almost seems like I need a lstBox.Refresh - except the method doesn't exist. Anybody have any ideas? Thanks.

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

    Re: ListBox Lunacy (XL 2003 SP2)

    Either method should work. Could you attach a small sample workbook with a userform that demonstrates the problem?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ListBox Lunacy (XL 2003 SP2)

    Thanks for the quick response, Hans. I started trying to cut down the size of the file but it's very large and was taking a long time. I tried one final work-around and it seems to do the trick. Instead of referring to lstBox.Value, I use lstBox.List(lstBox.lstIndex). Not ideal, but it works.
    I don't know what's going on with the .Value property that it can't be assigned a value. I can have successive lines of code ilke: lstBox.Value = "Item 3" followed by MsgBox lstBox.Value and it will display a blank (yet it will have populated .Listindex and .Selected(2) appropriately). Very odd.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox Lunacy (XL 2003 SP2)

    What is the MultiSelect property set to?

    I *think* setting the Value property only selects an item when MultiSelect is set to fmMultiSelectSingle
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: ListBox Lunacy (XL 2003 SP2)

    > I *think* setting the Value property only selects an item when MultiSelect is set to fmMultiSelectSingle

    That is entirely true, but what's more, trying to set the Value results in an error message. Using lstbox.Selected(2) = True should work for a multi-select list box, however, and Colin reports that that doesn't work either...

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ListBox Lunacy (XL 2003 SP2)

    It *is* set to zero. I think you're right, though. Actually I'd guess it would generate an error if the .Value property was used on a multi-select list box.
    Just thought of something. I wonder if the way I loaded the list box is a factor. The item names are stored in an Excel range, but I loaded them using a variant as follows:
    vaList = Names("tblItems").RefersToRange.Value and then lstBox.List() = vaList
    Do you think that could be a factor?

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

    Re: ListBox Lunacy (XL 2003 SP2)

    In such a setup, you should still be able to use lstBox.Selected(2) = True or lstBox.ListIndex = 2.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ListBox Lunacy (XL 2003 SP2)

    Right. It's not that I can't use either the .Selected or the .ListIndex syntax. They both work inasmuch as they do "stick". The problem is that the .Value property doesn't reflect the change. It's like I need to Calc the listbox to fully update it. Using lstBox.List(lstBox.ListIndex) seems to always work.
    I had thought I'd run into the phenomenon before and noticed some other code just now. It related to a combo box, but it was the same deal.
    I had to code it as cboBox.List(cboBox.ListIndex) in order to pick up the user's selection.

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

    Re: ListBox Lunacy (XL 2003 SP2)

    Hmm, if I select an item using Selected(3) = True or using ListIndex = 3, the Value property reflects this. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ListBox Lunacy (XL 2003 SP2)

    Are the BoundColumn and ColumnCount both 1, assuming the range you fill the list from is only one column?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: ListBox Lunacy (XL 2003 SP2)

    Yes, both set to 1. The annoying thing is that if I try to replicate the issue on a new workbook, it works as it should.
    It is a very complicated interrelated workbook, so would be very time consuming to reduce it to a small workable version that demonstrates the problem. (9 userforms, 13 modules, 17 worksheets). I tried running Rob Bovey's CodeCleaner, but that didn't help.
    The userform actually has 4 listboxes that I'm tying to populate and preselect. I tried deleting and recreating the listboxes one at a time. I thought I was on to something because the first two started to work okay, but when I recreated the third one the problem resurfaced. The odd thing is that each of the 4 have had the problem, but it seems to be random as to which ones will work and which ones won't.
    Thanks to you and the others for your help. Clumsy as it is, the lstBox.List(lstBox.ListIndex) seems to work consistently, so I'll leave it at that for now. If I have time, I may try cutting down the file and posting it for you guys. Or if I ever figure it out and it would be of broader interest, I'll post that.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ListBox Lunacy (XL 2003 SP2)

    If you like, and if there's nothing confidential in it, you could zip and email it to me and I will see if there's anything immediately obvious behind the behaviour, and post the findings back here.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #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: ListBox Lunacy (XL 2003 SP2)

    > trying to set the Value results in an error message
    My "not even" 2c worth.
    I suspect that one can't really "set" a value in the generally accepted sense of assigning a value (to a variable etc.), that a ListBox can only get values put into it by an <font color=448800>.Add</font color=448800> method.

    I can't really think what <font color=red>.Value=</font color=red> might mean.

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

    Re: ListBox Lunacy (XL 2003 SP2)

    You *can* set the Value of a list box that has been populated using AddItem., but only if its MultiSelect property has been set to 0 - frmMultiSelectSingle, in other words if you can *not* select multiple items. The Value of the list box corresponds to the selected item.

Posting Permissions

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