Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extend Selection of Listbox (Excel 98 thru 2002)

    I'm trying to set up a forms list box control with the selection type set to "Extend". It works great except I can't figure out how to retrieve the items that are selected!! When it was set to "single" the index number of the value goes to the cell specified in Cell Link. I read something in the Microsoft Knowledge Base that the only way to retrieve the selected items when the type is "Multi" or "Extend" is with VBA code. Ok, but I can't find any examples of how to do this. I be happy if I could just get a list of the index numbers of the selections in a cell or a range of cells similar to the way the index to the selected item gets put into the cell link if the selection type is "Single". Can anybody help me please????

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    The following loops through all items (the first has an index value of 0) in a listbox and prints the items that are select to the immediate wind :<pre>For i = 0 To (ListBox1.ListCount) - 1
    If ListBox1.Selected(i) = True Then
    Debug.Print "Item " & i & " selected"
    End If
    Next</pre>

    If the item is selected the selected property = True, otherwise False.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    I found this in the Help file, does it help?

    <hr>
    Selected Property


    Returns or sets the selection state of items in a ListBox.

    Syntax

    object.Selected( index ) [= Boolean]

    The Selected property syntax has these parts:

    Part Description
    object Required. A valid object.
    index Required. An integer with a range from 0 to one less than the number of items in the list.
    Boolean Optional. Whether an item is selected.


    Settings

    The settings for Boolean are:

    Value Description
    True The item is selected.
    False The item is not selected.


    Remarks

    The Selected property is useful when users can make multiple selections. You can use this property to determine the selected rows in a multi-select list box. You can also use this property to select or deselect rows in a list from code.

    The default value of this property is based on the current selection state of the ListBox.

    For single-selection list boxes, the Value or ListIndex properties are recommended for getting and setting the selection. In this case, ListIndex returns the index of the selected item. However, in a multiple selection, ListIndex returns the index of the row contained within the focus rectangle, regardless of whether the row is actually selected.

    When a list box control's MultiSelect property is set to None, only one row can have its Selected property set to True.

    Entering a value that is out of range for the index does not generate an error message, but does not set a property for any item in the list.
    <hr>
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    Sounds promising, thanks to both of you for the input, but I'm having trouble getting past the name of the listbox. I went to "Assign Macro" on the menu and it gave me the name of the default macro (in this case ListBox27_Change()) so I assumed the listbox name would be listbox27. So I added code to the macro "If ListBox27.Selected(0) = true then..." but when I click on an item in the list I get a pop up window with "Run-Time Error '424' Object Required" and the de###### puts the yellow highlight on the line with my if statement. What am I doing wrong?
    Thanks

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    It seems you are using a Listbox from the Forms Toolbar, rather than the Control tools. The example I offered was based on the assumption you were using a Listbox on a UserForm. However if you use a listbox from the Control toolbox, the samr approach applies. However the best place for the code is in the Workshhet codepane. To access that right click on the sheet tab and select View code. See the attached graphic. The example included uses the LostFocus event fill a range of cells to indicate the state of each item.

    You can select different events to code for from the right hand side dropdown of the codepane.

    Andrew C
    Attached Images Attached Images

  6. #6
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    GLUB, GLUB, GLUB... I think I'm in WAY over my head!!!!
    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    I was hoping for a much simpler solution. You are delving into areas about which I know nothing... yet. I do appreciate the help but I'm afraid I'm too far behind to catch up. I can't even figure out how to assign the input range to the control list box the way I did for the forms listbox. The Format Control menu option window does not have a Control tab like the Forms List Box does. I see where the code goes that you mentioned and it seems to think this control is ListBox1. Is there an easier way out?? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    Thanks

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    Maybe you could describe what you're trying to do in the workbook (more generally) and someone will suggest an entirely different approach that avoids this irritating little listbox altogether.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    If you want to use a Listbox from the Forms toolbar, try smething like the following. It uses a Listbox called Listing which has a predefined Input Range. Assign the following code to a button, which when clicked will fill a range starting at A1 with all selected items. A demo workbook is attached.<pre>Sub GetListSelections()
    Dim Listing1 As ListBox
    Dim ListArray As Variant
    Dim Item As Long, i As Long
    [A:A].ClearContents
    Set Listing1 = ActiveSheet.ListBoxes("Listing")
    ListArray = Listing1.Selected
    For i = 1 To UBound(ListArray)
    If ListArray(i) = True Then
    [A1].Offset(Item, 0) = Listing1.List(i)
    Item = Item + 1
    End If
    Next
    End Sub</pre>

    Hope it gets you started.

    Andrew C
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    A Grand Idea, Sister! In fact, in my struggles over all this I've thought of a couple of different ways to accomplish my goal, the sad fact is I have a large workbook with many formulas based on two form listboxes that work fine when the selection type is set to single. I am trying to expand the functionality of this complex workbook without changing the way it looks to the people using it and without changing any more than I have to!
    Generally, I have a list with several columns of data one of which contains a reference number to another list. The other list is a numbered list with two columns. I then use this data to choose rows from the first list according to data in one column via a listbox. I then further limit the selected rows by selecting from the included reference numbers in the second listbox.
    I've attached a print screen image of a simplified example.
    In this example the first listbox shows a sorted list of unique automobile models. When an item is chosen from the first listbox the second listbox is populated with a sorted list of unique color combinations taken from the first list. When an item is chosen from this list a line is generated that displays a sentence using the selected data. I would like to expand this to allow choosing two or more color references and then generate two or more sentences.
    I hope this is general enough. I do hope someone can help. If I could just figure out how to retrieve the selections from the pesky form listbox I'd be able to work out the rest.
    Thanks
    Attached Images Attached Images

  10. #10
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    Wow! Andrew, this works! I opened the attachement and played a bit with it. I even assigned your macro to the listbox and it works without using the button. Now I'll try to make this work in my workbook!!
    Thanks!

  11. #11
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    I was able to put this into my worksheet and it works perfectly!! Thanks so much for your help!! Now if I could just figure out where you learn all this stuff, 'cause it sure isn't clear in the Excel Help!!
    Thanks

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    It will work wihot the button, bur be aware that the code runs each time any one item in the listbox is selected. However if you are feeling adventurous, you can adapt it slightly and apply the same code to 2 different listboxes. On the attached sample I have set a different LinkedCell for both listboxes and assigned the following code to both :<pre>Sub GetListSelections()
    Dim Listing1 As ListBox
    Dim ListArray As Variant
    Dim Item As Long, i As Long
    Dim oCell As Range
    Set Listing1 = ActiveSheet.ListBoxes(Application.Caller)
    Set oCell = Range(Listing1.LinkedCell)
    ListArray = Listing1.Selected
    Range(oCell, oCell.End(xlDown)).ClearContents
    For i = 1 To UBound(ListArray)
    If ListArray(i) = True Then
    oCell.Offset(Item, 0) = Listing1.List(i)
    Item = Item + 1
    End If
    Next
    End Sub</pre>

    Something to experiment with.

    Andrew C
    Attached Files Attached Files

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    Hang around here and you will learn <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Andrew C

  14. #14
    New Lounger
    Join Date
    Apr 2002
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    Thanks! I will experiment with it. As far as the button is concerned, in my case I do want it to work in "real time" with the choices. And yes, I will hang around but I think it will be a long time before I am no longer "stymied".
    Thanks <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extend Selection of Listbox (Excel 98 thru 2002)

    Andrew,

    This was a good exercise. I understand the User Form version but when I try to apply the ListBox from the Custom Toolbar I have difficulty transitioning Set Listing1 = ActiveSheet.ListBoxes("Listing"). The range "Listing" can not be assigned to the ToolBar object.

    Any advice would truly be appreciated.
    John

Page 1 of 2 12 LastLast

Posting Permissions

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