Loop thru items in listbox to see if selected. Take selected items, copy selected items. Then paste selected items into spreadsheet.

[K3113tt]

a ListBox starts with zero. To be able to select multiple items in the ListBox you need to change the MultiSelect property to 1 in the Property window. Remember to use ALT-F11 keys to get to Excel's code window. Clear the first 10 rows in

column B in the Activate event of the UserForm to clear the output area of spreadsheet before presenting the form. Use With-End With statements in code to set Font.Size to 14 and Font.Bold to True in Cells B1:B10



Hint: Use the Index for the subscript for the Selected and List properties of the ListBox to get them to work properly. Example: lstListBox.Selected(intIndex) and lstListBox.List(intIndex)



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

 

[DanteAmor]

Hi and welcome to MrExcel!

Put the following in your userform

Private Sub CommandButton1_Click()
  Dim intCounter As Integer
  Dim i As Integer
  intCounter = 1
  For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
      Cells(intCounter, "B").Value = ListBox1.List(i)
      intCounter = intCounter + 1
    End If
  Next
End Sub

Private Sub UserForm_Activate()
  ListBox1.MultiSelect = fmMultiSelectMulti
  ListBox1.List = Range("A1:A10").Value
  With Range("B1:B10")
    .ClearContents
    .Font.Size = 14
    .Font.Bold = True
  End With
End Sub

 

[K3113tt]

Thank you so much!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Jon von der Heyden]

Suspected homework: Pizza