Select all listbox vba

For a Microsoft Word 98 Macintosh Edition version of this article, see 201669.

This article describes how to retrieve selected items from a
ListBox control that makes it possible for you to select multiple values.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. In a UserForm, when you set the MultiSelect property to 1 - fmMultiSelectMulti for a ListBox control, you can select any number of items from a list. For example, if a list contains the days of the week [Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday], you can select any, none, or all the items.To determine the items that are selected, you can use the Selected property of the list box. The Selected property of a list box is an array of values where each value is either True [if the item is selected] or False [if the item is not selected].

For example, if the list contains the seven days of the week and Sunday, Tuesday, and Saturday are selected, the Selected property array would contain the following values:

True, False, True, False, False, False, TrueThis is true because the first item [Sunday] is selected, the second item [Monday] is not selected, the third item [Tuesday] is selected, the fourth through the sixth items [Wednesday, Thursday, and Friday] are not selected, and the seventh item [Saturday] is selected.

The following macro code provides two methods for using the selected data from the ListBox. The first method uses one selected item at a time, and the second method builds a list of all the selected items.Note These steps for creating a UserForm in the Visual Basic Editor assume that you have an understanding of Visual Basic for Applications, Microsoft Word, and Microsoft Forms design and tools.

1. Start the Visual Basic Editor. To do this, press ALT+F11.

2. If the Properties dialog box is not visible, click Properties on the View menu.
   

3. If the Project Explorer window is not visible, click
   Project Explorer on the View menu.

4. On the Insert menu, click
   UserForm.

5. Click the ListBox control on the
   Controls Toolbox, and then drag it to the UserForm.

6. In the Properties-ListBox1 window, change the MultiSelect property to the 1 - fmMultiSelectMulti value.

7. Click the CommandButton control on the
   Controls Toolbox, and then drag it to the UserForm to put the CommandButton1 control on the UserForm.

8. Repeat step 7 to put a second
   CommandButton on the UserForm. This puts the
   CommandButton2 control on the UserForm.

9. Double-click the UserForm to display the Code window for the UserForm.

10. Press PAGE DOWN, and then type the following macro code for the Userform_Initialize and the CommandButton_Click events:

Private Sub UserForm_Initialize[] 'Creates and assigns the Array to the ListBox when the form loads. Dim mylist As Variant mylist = Array["Sunday", "Monday", "Tuesday", "Wednesday", _ "Thursday", "Friday", "Saturday"] ListBox1.List = mylistEnd SubPrivate Sub CommandButton1_Click[] 'First Method: Displays individual selections one at a time. For x = 0 To ListBox1.ListCount - 1 If ListBox1.Selected[x] = True Then MsgBox ListBox1.List[x] End If Next x Unload MeEnd SubPrivate Sub CommandButton2_Click[] 'Second Method: Displays all the items in one message box Dim msg As String For x = 0 To ListBox1.ListCount - 1 If ListBox1.Selected[x] = True Then msg = msg & ListBox1.List[x] & vbCrLf End If Next x MsgBox "You have selected " & vbCrLf & msg Unload Me 'Uncomment the following two line to insert the variable 'into a document. 'Documents.Add 'Selection.TypeText Text:= msgEnd Sub

The MultiSelect property in Excel VBA allows a user to select multiple items in a list box. The Userform we are going to create looks as follows:

To create this Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the list boxes [first at the left, the second at the right], command buttons, check boxes [first at the left, the second at the right], frame and option buttons [first at the top, the second below the first, and so on]. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a list box control by clicking on ListBox from the Toolbox. Next, you can drag a list box on the Userform. When you arrive at the 'Select Type' frame, remember to draw this frame first before you place the three option buttons in it.

4. You can change the names and the captions of the controls. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of the controls, but it is not necessary here because we only have a few controls in this example. To change the caption of the Userform, command buttons, check boxes, frame and option buttons, click View, Properties Window and click on each control.

5. To show the Userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click[] UserForm1.Show

End Sub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

6. Open the Visual Basic Editor.

7. In the Project Explorer, right click on UserForm1 and then click View Code.

8. First, declare the variable i of type Integer. Declare the variable in the General Declarations section [at the top of the code]. This way you only have to declare the variable once and you can use them in multiple subs.

Dim i As Integer

9. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

10. Add the following code lines:

Private Sub UserForm_Initialize[]

With ListBox1

    .AddItem "Sales"     .AddItem "Production"     .AddItem "Logistics"     .AddItem "Human Resources"

End With

OptionButton3.Value = True

End Sub

Explanation: the first list box will be filled and the third option button is set as default.

We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons or the other controls.

11. In the Project Explorer, double click on UserForm1.

12. Double click on the Add button.

13. Add the following code lines:

Private Sub CommandButton1_Click[]

For i = 0 To ListBox1.ListCount - 1

    If ListBox1.Selected[i] = True Then ListBox2.AddItem ListBox1.List[i]
Next i

End Sub

Explanation: Excel VBA loops through the first list box [list index number of zero [0] for the first item in the list] and, if selected, adds the item to the second list box.

14. Double click on the Remove button.

15. Add the following code lines:

Private Sub CommandButton2_Click[]

Dim counter As Integer

counter = 0

For i = 0 To ListBox2.ListCount - 1

    If ListBox2.Selected[i - counter] Then         ListBox2.RemoveItem [i - counter]         counter = counter + 1

    End If

Next i

CheckBox2.Value = False

End Sub

Explanation: Excel VBA loops through the second list box and, if selected, removes the item. The counter variable holds track of the number of removed items.

16. Double click on the first option button.

17. Add the following code lines:

Private Sub OptionButton1_Click[] ListBox1.MultiSelect = 0 ListBox2.MultiSelect = 0

End Sub

18. Double click on the second option button.

19. Add the following code lines:

Private Sub OptionButton2_Click[] ListBox1.MultiSelect = 1 ListBox2.MultiSelect = 1

End Sub

20. Double click on the third option button.

21. Add the following code lines:

Private Sub OptionButton3_Click[] ListBox1.MultiSelect = 2 ListBox2.MultiSelect = 2

End Sub

Explanation: the 'Select Type' setting can be chosen by clicking on the option buttons. The picture of the Userform shown earlier gives a description of each setting. Instead of configuring this setting at runtime, you can also configure this setting at design time. To achieve this, right mouse click on a list box control, and then click on Properties. Set the MultiSelect property to 0 - fmMultiSelectSingle, 1 - fmMultiSelectMulti or 2 - fmMultiSelectExtented.

22. Double click on the first check box.

23. Add the following code lines:

Private Sub CheckBox1_Click[]

If CheckBox1.Value = True Then

    For i = 0 To ListBox1.ListCount - 1         ListBox1.Selected[i] = True

    Next i

End If

If CheckBox1.Value = False Then

    For i = 0 To ListBox1.ListCount - 1         ListBox1.Selected[i] = False

    Next i

End If

End Sub

Explanation: by checking the first check box, all the items of the first list box can be selected / deselected.

24. Double click on the second check box to add the same code lines. Only replace CheckBox1 with CheckBox2 and ListBox1 with ListBox2.