Fantastic Multi Select Listbox.

In this tutorial we will be looking at multi select listboxes. This is an Excel VBA tutorial for Windows operating systems..
This fantastic multi select listbox will enable you to select multiple rows in your listbox and send all or some of the row values to any destination in your file.
You can select just one row or multiple rows then when you click the command button and the values will be sent to the destination sheet.
The completed file is available for download below.

This is the first of three tutorials.

Tutorial 1: Fantastic Multi Select Listbox -Add Multiple. Adding multiple listbox selections to a destination worksheet.

Tutorial 2: Fantastic Multi Select Listbox – Edit Multiple. Edit multiple selected listbox rows.

 

Tutorial 3: Fantastic Multi Select Listbox – Delete Multiple. Delete multiple selected listbox rows.

Video: Fantastic Multi Select Listbox

Name Range: Dynamic

MyData: =OFFSET(Data!$D$6,1,0,COUNTA(Data!$D$7:$D$10000),8)

Listbox Creation

multi select listbox userform

You will need 1 x Userform: Named, frmMulti
1 x Label: Add your description here.
1 x Listbox: Named, lstMulti
4. x Command buttons. Named, cmdAdd / cmdAdd2 / cmdRefresh / cmdClose
Make sure that if you are using the code below that you name these controls exactly as I have typed them above. You will notice that each macro incorporates the names of these controls.

Properties for the listbox

Fantastic multi select listbox properties

In your listbox properties make sure that you have added the dynamic named range in the Row source and also that you have enabled multi select. The number of columns will bring 9.

VBA Code Multi Select Listbox

Code for the multi select listbox userform buttons
This code goes into the userform VBA editor

Private Sub cmdAdd_Click()
'dimension the variable
Dim addme As Range, cNum As Integer
Dim x As Integer, y As Integer, Ck As Integer
'set variables
Set addme = Sheet1.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
cNum = 7
Ck = 0
'run the for loop
For x = 0 To Me.lstMulti.ListCount – 1
'add condition statement
If Me.lstMulti.Selected(x) Then
Ck = 1
'second loop
For y = 0 To cNum
addme.Offset(0, y) = Me.lstMulti.List(x, y)
Next y
Set addme = addme.Offset(1, 0)
End If
'clear the selected values
lstMulti.Selected(x) = False
Next x
'send a message if nothing is selected
If Ck = 0 Then
MsgBox "There is nothing selected"
End If
End Sub

Private Sub cmdAdd2_Click()
'dimension the variable
Dim addme As Range
Dim x As Integer, Ck As Integer
'set variables
Set addme = Sheet1.Cells(Rows.Count, 14).End(xlUp).Offset(1, 0)
Ck = 0
'run the for loop
For x = 0 To lstMulti.ListCount – 1
'add condition statement
If Me.lstMulti.Selected(x) Then
Ck = 1
addme = Me.lstMulti.List(x)
addme.Offset(0, 1) = Me.lstMulti.List(x, 1)
addme.Offset(0, 2) = Me.lstMulti.List(x, 6)
addme.Offset(0, 3) = Me.lstMulti.List(x, 7)

Set addme = addme.Offset(1, 0)
'clear the selected row
lstMulti.Selected(x) = False
End If
Next x
'send a message if nothing is selected
If Ck = 0 Then
MsgBox "There is nothing selected"
End If
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdRefresh_Click()
Dim x
For x = 0 To Me.lstMulti.ListCount – 1
If Me.lstMulti.Selected(x) Then Me.lstMulti.Selected(x) = False
Next x
End Sub

This code goes into a module.

It is the code that calls the userform. Assign it to the shape on the interface sheet. You can do this by right clicking the shape and choosing assign macro.

Sub ShowMe()
frmMulti.Show
End Sub

Template to download

Template Add Fantastic Multi Select Listbox

I have supplied the template here for the multi select listbox for those of you who wish to participate and develop this tutorial yourself.

Completed file to download

Here is the completed file you to download. You can compare this to see if you make any mistakes in your tutorial.

Add Fantastic Multi Select Listbox

Our next tutorial will demonstrate how we can edit multiple rows in a database from a multi-selected listbox.
I hope you have enjoyed this tutorial.
My best wishes
Trev