Extend Selection of Listbox (Excel 98 thru 2002)

Topic

New Reply

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

Reply | Quote

Replies

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 :

For i = 0 To (ListBox1.ListCount) - 1
    If ListBox1.Selected(i) = True Then
        Debug.Print "Item " & i & " selected"
    End If
Next

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

Andrew C

Reply | Quote

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

Reply | Quote

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

Reply | Quote

GLUB, GLUB, GLUB… I think I’m in WAY over my head!!!!

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

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

Hope it gets you started.

Andrew C

Reply | Quote

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!

Reply | Quote

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 :

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

Something to experiment with.

Andrew C

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hang around here and you will learn

Andrew C

Reply | Quote

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

Reply | Quote

ActiveSheet.ListBoxes(“Listing”)
Refers to a listbox NAMED “listing”, not a defined range named “listing”

Steve

Reply | Quote

Unfortunately I receive the following error message when refering to the listbox NAMED “LISTING”: Unable to get the listboxes property of the worksheet class”

Reply | Quote

I only have a vague recollection of this, but the code is designed to be attached to the listbox on the worksheet, and not to a toolbar button. It should respond when you activate one or other of the listboxes, and if you make multiple selectiond from either, the value should be entered on cells adjacent (row offset) to the relevant linked cell.

Andrew C

Reply | Quote

Andrew,

I’m still puzzled on the Control Toolbar listbox approach. I’ve attached my test file. If you don’t mind taking a look at it, I would certainly appreciate it.

Thanks,
John

Reply | Quote

John,

Because the ActiveX controls you are using do not support the Application.Caller property, and because of other differences the code needs some amendment. Also you need to set the MultiSelect property of the Listbox to either Multi or Extended (not Single) depending on your exact needs. The following code should get you started :

    Private Sub ListBox1_Change()
    Dim Indx As Long, Items As Long, i As Long
    Dim oCell As Range
        Items = ListBox1.ListCount - 1
        Set oCell = Range(ListBox1.LinkedCell)
        Range(oCell, oCell.Offset(Items, 0)).ClearContents
        For i = 0 To Items
            If ListBox1.Selected(i) = True Then
                oCell.Offset(Indx, 0) = ListBox1.List(i)
                Indx = Indx + 1
            End If
        Next
    End Sub

Also attached your example with th eabove code using MultiSelectExtended

Andrew

Reply | Quote

Many Thanks,
John

Reply | Quote

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

[indent]

---

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.

---

[/indent]

Reply | Quote