VBA copying information from a listbox to a different column

Question

Hello everyone this is my first and hopefully last question I will ask on here. I am having trouble with vba in excel and I was hoping that someone could answer it

Hints for the Copy command button code: Use a For -Next loop to go through each item of the list box to see if it were selected. Remember a list box is zero-based. Use the lstListBox.ListCount property of the list to end the For-Next loop. (lstListBox.ListCount - 1) Within the For-Next loop use an If statement to see whether an item in the list is selected using the lstListBox.Selected(intIndex) property which is of Boolean data type. If a list value is selected increment a counter (intCounter) to keep track of how many items were selected and put in a statement to display the selected list value to a row in column B of the spreadsheet. Use this counter in the cells object row number to display the selected item in the list to column B of the spreadsheet. example: Cells(intCounter,"B").Value = lstListBox.List(intIndex) See the pseudocode listed below: (These statements can be used as comments in your code) Loop thru the ten items in the list Check if an item in the list is selected If Yes then increment a counter display the selected item from the list in the counter number row of column B of the spreadsheet End the Check or Select structure End the Loop structure P.S. Convert the pseudocode listed above to For-Next and If-End If looping and select coding structures.

So what I am really trying to do is copy some information from a listbox into a different column. If anyone could help that would be great. Can't go home until I have this resolved. This is what I have so far it could all be garbage I really don't know

Private Sub CommandButton1_Click() Dim intNumberOfItemsInList As Integer Dim lstListBoxListCount As Integer Dim ItemsCount As Integer Dim strInput As String Dim intIndex As Integer Dim lstlistbox As ListBox Dim intcounter As Integer

'Loop thru the ten items in the list For intNumberOfItemsInList = 0 To 9 'Check if an item in the list is selected If Yes then

Next lstlistbox.ListCount - 1
    'increment a counter
    Cells(intcounter, "B").Value = lstlistbox.List(intIndex)
   ' display the selected item from the list in the counter number row of column B of the spreadsheet
   Range("B1:B10").Select
ActiveSheet.Paste
'End the Check or Select structure

End Select

'End the Loop structure

End Sub

The comments are supposed to help "guide us" through the program, but it has not helped me. Thanks again. If you have any questions about the question please let me know. I will try to answer them to the best of my ability.

was my answer what you were looking for? If it was please upvote and mark as correct. If not I can edit it to better match your expectations. Thank you. — Ryan G

Ryan G Ryan G · Accepted Answer · 2013-10-30T23:15:10

If you only need to copy the list information to somewhere else the listbox object has a variable called ListFillRange which contains the string of the range the data is being pulled from.

option explicit
Sub copy_list_items()
    Dim ws as Worksheet
    Dim lstbox as Variant
    Set ws = Sheets("Sheet 1")
    Set lstbox = ws.Shapes("List Box 1").OLEFormat.Object
    /*'' The range for my list box was set using values from the same sheet 
    ''    a few columns away.
    '' Also, ignore/remove the slash star comments, as these will throw an error with 
    '' Excel VBA,*/
    ws.Range(ws.Cells(1,1),ws.Cells(lstbox.ListCount,1)).Value = _
                    ws.Range(lstbox.ListFillRange).Value
End Sub

Will this work in your case?

VBA copying information from a listbox to a different column

1 Answers