Listbox not returning data correctly in excel vba

Question

I have a listbox in a userform with 13 options, similar to:

What I am tying to get it to do is that when an item is chosen, it returns a specific result. By this I mean: chicken leg as 'a', nugget = 'b', burger = 'c', etc...

This is the code I've come up with:

Private Sub CommandButton1_Click()
Dim lItem As Long
    For lItem = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "a"
    ElseIf ListBox1.Selected(lItem) = 1 Then
        Worksheets(3).Range("E1").Value = "b"
    ElseIf ListBox1.Selected(lItem) = 2 Then
        Worksheets(3).Range("E1").Value = "c"
    ElseIf ListBox1.Selected(lItem) = 3 Then
        Worksheets(3).Range("E1").Value = "d"
    ElseIf ListBox1.Selected(lItem) = 4 Then
        Worksheets(3).Range("E1").Value = "e"
    ElseIf ListBox1.Selected(lItem) = 5 Then
        Worksheets(3).Range("E1").Value = "f"
    ElseIf ListBox1.Selected(lItem) = 6 Then
        Worksheets(3).Range("E1").Value = "fs"
    ElseIf ListBox1.Selected(lItem) = 7 Then
        Worksheets(3).Range("E1").Value = "g"
    ElseIf ListBox1.Selected(lItem) = 8 Then
        Worksheets(3).Range("E1").Value = "gs" 'its not a straight sequence as such
    ElseIf ListBox1.Selected(lItem) = 9 Then
        Worksheets(3).Range("E1").Value = "h"
    ElseIf ListBox1.Selected(lItem) = 10 Then
        Worksheets(3).Range("E1").Value = "i"
    ElseIf ListBox1.Selected(lItem) = 11 Then
        Worksheets(3).Range("E1").Value = "j"
    ElseIf ListBox1.Selected(lItem) = 12 Then
        Worksheets(3).Range("E1").Value = "js"
    '...................................................
    'from here on it is for when two items are selected'
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ElseIf ListBox1.Selected(lItem) = 1 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ab"
    ElseIf ListBox1.Selected(lItem) = 2 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ac"
    ElseIf ListBox1.Selected(lItem) = 3 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ad"
    ElseIf ListBox1.Selected(lItem) = 4 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ae"
    ElseIf ListBox1.Selected(lItem) = 5 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "af"
    ElseIf ListBox1.Selected(lItem) = 6 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "afs"
    ElseIf ListBox1.Selected(lItem) = 7 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ag"
    ElseIf ListBox1.Selected(lItem) = 8 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ags"
    ElseIf ListBox1.Selected(lItem) = 9 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ah"
    ElseIf ListBox1.Selected(lItem) = 10 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ai"
    ElseIf ListBox1.Selected(lItem) = 11 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "aj"
    ElseIf ListBox1.Selected(lItem) = 12 And ListBox1.Selected(lItem) = 0 Then
        Worksheets(3).Range("E1").Value = "ajs"
    Else
    '...................................................
    ' more code for many more selections               '
    ' i stopped when i realised it wasn't quite working'
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    End If
Next
End Sub

I hope it is clear what I am trying to achieve.

The problem is that, whatever is selected, only returns 'a', no matter how many are selected or whether 'chicken leg' is even selected. What is missing?

I suspect that it's something fairly fundamental, as I am new to vba :)

Another way I have also tried is:

Private Sub CommandButton1_Click()
    If ListBox1.Value = "chicken leg" Then
        Worksheets(3).Range("E1").Value = "a"
    ElseIf ListBox1.Value = "nugget" Then
        Worksheets(3).Range("E1").Value = "b"
    ElseIf ListBox1.Value = "burger" Then
        Worksheets(3).Range("E1").Value = "c"
    ElseIf ListBox1.Value = "sandwich" Then
        Worksheets(3).Range("E1").Value = "d"
    Else
End if
End sub

But that didn't work at all...

Can someone please help??

Thanks

.Selected requires a Boolean property so True or False. your 2nd code should work, are you positive the text equals exactly to what is in the ListBox? — DragonSamu
– DragonSamu, Commented Aug 26, 2016 at 10:33
yep, i copied it directly from the rowsource of the listbox — StanB
– StanB, Commented Aug 26, 2016 at 10:35
where does it stop in your 2nd code? I have tested it and I didn't have any problems — DragonSamu
– DragonSamu, Commented Aug 26, 2016 at 10:39
@DragonSamu I have updated the code above...i had only trialed it for the first few — StanB
– StanB, Commented Aug 26, 2016 at 10:47

Axel Richter · Accepted Answer · 2016-08-26 10:43:28Z

For a ListBox with fmMultiSelectMulti the following should work:

Private Sub CommandButton1_Click()
 Dim sResult As String
 Dim lItem As Long
 For lItem = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(lItem) And lItem = 0 Then
        sResult = sResult & "a"
    ElseIf ListBox1.Selected(lItem) And lItem = 1 Then
        sResult = sResult & "b"
    ElseIf ListBox1.Selected(lItem) And lItem = 2 Then
        sResult = sResult & "c"
    ElseIf ListBox1.Selected(lItem) And lItem = 3 Then
        sResult = sResult & "d"
    ElseIf ListBox1.Selected(lItem) And lItem = 4 Then
        sResult = sResult & "e"
    ElseIf ListBox1.Selected(lItem) And lItem = 5 Then
        sResult = sResult & "f"
    ElseIf ListBox1.Selected(lItem) And lItem = 6 Then
        sResult = sResult & "fs"
    ElseIf ListBox1.Selected(lItem) And lItem = 7 Then
        sResult = sResult & "g"
    ElseIf ListBox1.Selected(lItem) And lItem = 8 Then
        sResult = sResult & "gs" 'its not a straight sequence as such
    ElseIf ListBox1.Selected(lItem) And lItem = 9 Then
        sResult = sResult & "h"
    ElseIf ListBox1.Selected(lItem) And lItem = 10 Then
        sResult = sResult & "i"
    ElseIf ListBox1.Selected(lItem) And lItem = 11 Then
        sResult = sResult & "j"
    ElseIf ListBox1.Selected(lItem) And lItem = 12 Then
        sResult = sResult & "js"
    End If
 Next
 Worksheets(3).Range("E1").Value = sResult
End Sub

user3598756 · Accepted Answer · 2016-08-26 11:04:28Z

1

this should work AND be short...:

Option Explicit

    Private Sub CommandButton1_Click()
        Dim sResult As String
        Dim lItem As Long
        Dim sResultArr As Variant

        sResultArr = Array("a", "b", "c", "d", "e", "f", "fs", "g", "gs", "h", "i", "j", "js")

        For lItem = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(lItem) Then sResult = sResult & sResultArr(lItem)
        Next
        Worksheets(3).Range("E1").value = sResult
    End Sub

answered Aug 26, 2016 at 11:04

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

4 Comments

user3598756 Over a year ago

@StanB, did you try this?

StanB Over a year ago

nope but i can see it how it works +1, but you help on my next issue? I'm trying to do the same thing but to not have all the results in one cell but rather each on their own line...

user3598756 Over a year ago

as per this site rules new issues require new posts. you may want to comment here with its link as soon as you make it to have me informed of it

StanB Over a year ago

yep, here you go, thnx!

Collectives™ on Stack Overflow

Listbox not returning data correctly in excel vba

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related