Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    De Select List Box Item (Excel 2000 VBA)

    Does anyone know how to cancel a listbox selection
    I have a listbox in a userform which is used by the user to select an account code (via a click event)
    Then I hide the userform.
    My problem is that the next time I show the same form the previous selection is selected (highlighted) and that code cannot be selected.
    I need a way to deselect the item.
    I have tried setting the ListIndex to something else including null but this doesn't work.

    Does anyone know how to de select or reset the list box ???

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: De Select List Box Item (Excel 2000 VBA)

    Ralph,

    A couple of quick, untested suggestions:

    Put code in the UserForm_Activate event which resets the values in the list box back to the values it has when the userform is first loaded (or have you tried that and for some reason it's not working?).
    OR
    Unload rather than hide the userform, and load/show it each time rather than just show it.

    Gary

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: De Select List Box Item (Excel 2000 VBA)

    Have you tried setting the listindex to -1? That's the equivalent of no selection.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: De Select List Box Item (Excel 2000 VBA)

    Thanks for the Help

    I tried the first method by setting listindex to -1 as suggested by Charlotte that didn't work as I got an error.
    I don't know what the original values are to cancel the selection ?

    Your second suggestion sounds good but I don't know how to Unload the form, userform.unload and "UserformName".unload both give errors.

    Can you tell me how to unload my form ?

    The failure to cancel is only occuring in Excel 2000 under Win 2000 or Win 98
    Everything appears to work for Excel 97 under Win 98

    I also tried using ListBox1.Selected(Breaks.ListIndex) = False to cancel the selection
    this re-sets ListIndex to -1 but doesn't cancel the selection there is also very strange behaviour as below.


    Re ListIndex
    I have observed some strange behaviour when I put MsgBox ListBox1.ListIndex, vbOKOnly, "List Index" before or after Hide in my ListBox1_Click code

    ie
    Private Sub ListBox1_Click()
    ActiveCell.Value=ListBox1.Text
    ListBox1.Selected(Breaks.ListIndex) = False
    MsgBox ListBox1.ListIndex, vbOKOnly, "List Index"
    UserForm1.Hide
    end sub

    When it is before Hide it displays the Index of the Selected Item, but it does so twice !!!! ?????

    When it is after Hide it displays the Index -1 but it does so twice !!!! ?????

    There is only one msgBox in the Click Code !!!!!! ????

    The userform.Show is in a right click event for the workbook.

    The Form is populated via Named Ranges as below

    Private Sub UserForm1_Initialize()
    Dim array9 As Variant
    array9 = Union(Sheets("Account Codes").Range("Code"), Sheets("Account Codes").Range _("Description"))
    ListBox1.List = array9
    End Sub

    This occures in both Excel 2000 under Win 2000 and Excel 97 under Win 98

    The Message Box only appears once if I remove ListBox1.Selected(Breaks.ListIndex) = False
    and in Excel 97 it all works even without cancelling the selection.

    This should be real simple but it is turning ito a nightmare.

    Any Help would be greatly appreciated


    Ralph


    PS the Solution suggested by Charlotte does work but you have to put in the .show rather than trying to reset after the .hide

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: De Select List Box Item (Excel 2000 VBA)

    My appologies your solution to set ListIndex =-1 does fix the Problem
    when placed imediatly before the .show

    Thanks

  6. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: De Select List Box Item (Excel 2000 VBA)

    Ralph,

    Sounds like Charlotte's suggestion worked for you.
    With regard to unloading the userform, if you ever need to do that, the syntax is simply:

    Unload Me

    Gary

Posting Permissions

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