Ask Experts Questions for FREE Help !
Ask
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #1

    Nov 6, 2013, 10:12 AM
    Using VBA to extract ListBox items
    I'm trying to create a module that will take selections from an activeX Listbox on a Sheet called Setup and transfer them to another sheet called Lists. On the Lists sheet, I want to store the selected items in cell L2 and down. So for example, if there were 4 items selected from the Listbox, cells L2:L5 would be populated with those values.

    Can anyone help me with this code?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Nov 6, 2013, 12:22 PM
    So I don't have to create a pseudo workbook to test it, can you post your workbook as it is currently with any helpful notes within?

    This will be a loop through the ListBox.Items to determine each one that is selected and place them on the other sheet.

    Also, what if L2:L5 has data already? Should some cells be cleared first? How many cells?
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #3

    Nov 6, 2013, 02:41 PM
    Thanks, Jerry. I was able to find some code and I got it to work, finally.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Nov 6, 2013, 05:19 PM
    Care to share? Others who search on their own may find this thread and your code snippet may help them.
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
     
    #5

    Nov 7, 2013, 07:31 AM
    Sure, here's my code:

    Sub SetupSelection()

    Dim UserSels() As Variant
    Dim SelCnt As Long
    Dim i As Long

    SelCnt = 0
    With Worksheets("Setup").ListBox1
    For i = 0 To .ListCount - 1
    If .Selected(i) Then
    SelCnt = SelCnt + 1
    ReDim Preserve UserSels(1 To SelCnt)
    UserSels(SelCnt) = .List(i)
    End If
    Next i
    End With

    If SelCnt > 0 Then
    Worksheets("Lists").Range("L2").Resize(UBound(User Sels)).Value = Application.Transpose(UserSels)
    Else
    MsgBox "No selections were made", vbInformation
    End If



    End Sub
    But I did encounter another issue that maybe you can help with. Inside of the ListBox, I cannot scroll up and down within the list using my mouse scroll. I have to manually click on the scroll bar to move up and down.

    Any solution?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #6

    Nov 7, 2013, 04:59 PM
    Read through this thread, it offers a hefty solution to this simple problem with no simple answer:

    ListBox Mouse Scroll

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

VB.NET listbox question [ 1 Answers ]

Greetings, I need to access a certain item in a listbox and then save it to a textbox. For example, I have a listbox with 5 items and 5 textboxes, and I need one item per textbox. What is the code necessary to access individual items in a textbox?

How to determine Net income, discontinued items, extraordinary items [ 0 Answers ]

Sales revenue 300,000 Loss on sale of investments 22,000 Interest revenue 4,000 Loss from flood damage (unusual and infrequent) 50,000 Cost of goods sold 160,000 General and administrative expenses 40,000 Restructuring costs 50,000 ...

ListBox problem [ 1 Answers ]

If lstSchedule.Items.Contains((0)) = True Then objWriter.WriteLine((0)) End If If lstSchedule.Items.Contains((1)) = True Then objWriter.WriteLine((1)) -----------------------until objWriter.WriteLine((7))--------------------- is this...


View more questions Search