Listbox issue

[The Grim Discovery]

Hi Excel peoples

I'm using a Listbox which contains three selectable items - the idea being that people can select any number/combination of the three as they see fit. It works fine but the linked cell reports '0' when more than 1 item on the listbox is selected. I've read around and the only answer seems to be use VBA which I dont understand and makes my brain hurt.

Is there a quick fix for this or an alternative that anybody would recommend? The key features being that any permutation of the three must be selectable and that whatever is picked can be counted easily.

Thanks in advance for your time & willingness to share knowledge.

 

[ranman256]

for multi pick lists, you have to scan the entire list to determine what is selected....

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True  Then msgbox ListBox1.List(i)
Next i

 

[mikerickson]

Which kind of ListBox are you using? Userform, ActiveX or from the Forms menu?

 

[The Grim Discovery]

It's under Form Controls

 

[The Grim Discovery]

Thanks Ranman - forgive my lack of knowledge - but what do I do with that code

 

[mikerickson]

What you are looking for is difficult with Forms ListBoxes.
Three checkboxes might be better.

If you need to use a listbox, you could put this as the control's Assigned Macro

Sub ListBox1_Change()
    Dim CheckedSum As Long, i As Long
    With ActiveSheet.Shapes("List Box 1").OLEFormat.Object
        For i = 1 To .ListCount
        CheckedSum = CheckedSum - ((2 ^ (i - 1)) * .Selected(i))
        Next i
    End With
    Range("B1").Value = CheckedSum
End Sub

It will put a number in B1 that represents which items on the list are checked
0- no checked
1- item 1 checked
2 - item 2 checked
3- items 1 and 3 checked
4 - item 3 checked
5 - items 1 and 3 checked
6 - items 2 and 3 checked
7 - items 1, 2 and 3 checked

etc.

(think of numbers in binary 010 means item 2 checked)

 

[The Grim Discovery]

many thanks Mike - sorted