Selected items in a listbox as text in specified cells

[apurvaoka]

Dear All,

I have searched for a resolution of this query a lot today and didnt find an answer; hence posting it here.

Theres a list of 5 items in my Sheet1. I want to create a listbox out of those items for selection. And then there are some formulae below that will work with reference to those selected items (vlookup, sumif etc).

I'd use data validation and prepare my data but with only one selection at a time. I want to have multiple selections possible and hence; listbox. I am new to VBA and do not know much about writing the codes.

If I can get the selected items in a listbox as text in specified cells; the rest of the formulae can work. My question; how can I get the selected items in a listbox as text in specified cells? (assume cells A1 to A5) What should be the code?

Any help is highly appreciated.
Regards,
Apurva Oka.

 

[paddydive]

Hi Apurva,

Try this.... Please make changes wherever necessary (like your listbox name etc)

Sub selectedListbox()
 
    k = 1
    With Sheet1.Shapes("Lbox1").OLEFormat.Object
        For i = 1 To .ListCount
            If .Selected(i) Then
                Cells(k, 1).Value = .List(i)
                k = k + 1
            End If
        Next
    End With
End Sub

 

[apurvaoka]

Hi,

Thanks for the quick response. My code looks as below.

Sub ListBox1_Change()
Sub selectedListbox()

k = 1
With Sheet1.Shapes("Lbox1").OLEFormat.Object
For i = 1 To .ListCount
If .Selected(i) Then
Cells(k, 1).Value = .List(i)
k = k + 1
End If
Next
End With
End Sub
End Sub


Is there anything wrong with it? Because I get the below error:

Compile Error:
Expected End Sub

and the first line i.e. Sub ListBox1_Change() is highlighted.

 

[tmhopfne]

you have nested "subs"
delete the first and the last line i.e., "sub listbox1_change()..... end sub"

 

[paddydive]

You have nested sub... delete the first & last line
Sub ListBox1_Change()
End Sub

Also check the sheet name.. i have assumed that your listbox is on sheet1 so you need to make the changes accordingly

 

[apurvaoka]

I get this error message

Cannor run the macro `Book1!ListBox1_Change.' The macro may not be available...... etc. Macros Are enabled.

 

[paddydive]

Please read the earlier comment from me and tmhopfne

you need to delete these lines

 

[apurvaoka]

I did. I cannot attach screenshot here somehow or I could have explained my query better. Anyways; let me try for some more time or else I will post a screenshot here later during the day. Thanks for your support really.

 

[cyrilbrd]

...I'd use data validation and prepare my data but with only one selection at a time. I want to have multiple selections possible and hence; listbox...

Hi, just being curious, when you say that you want multiple selections, does it mean you would like the user to be able to select from a multiple choice? If yes why not using data validation?

 

[paddydive]

Hi Cyrilbrd.

I understand he want to have multiple selections at a time hence choose list box, multiple-selection option will not be possible in data validation.