|
|
|
|
Senior Member
|
|
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?
|
|
|
Software Expert
|
|
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?
|
|
|
Senior Member
|
|
Nov 6, 2013, 02:41 PM
|
|
Thanks, Jerry. I was able to find some code and I got it to work, finally.
|
|
|
Software Expert
|
|
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.
|
|
|
Senior Member
|
|
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?
|
|
|
Software Expert
|
|
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
|
|
Question Tools |
Search this Question |
|
|
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?
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
|