Print Multiple Items from Listbox


Board Regular
Jan 12, 2016
Hey all,

First I will describe what I am trying to do and then I'll do my best to describe the situation. I am printing envelopes for multiple people each week, sometimes a week there will be more envelopes sent out versus other weeks. This requires me to select who I am sending to, load the envelopes and print. I believe I have set up a UserForm and the necessary tools to do this from Excel.

I have a ListBox within a UserForm with the multi-select option turned on. The ListBox is populated from a list of business "names" concatenated with their "attention" in Column H (this is generated from Columns A and B).

Column A contains the "Name" of the business
Column B contains the "Attn:" part
Column C contains the "Street Address"
Column D contains the "City"
Column E contains the "State"
Column F contains the "Zip"
Column H contains the "Name"&" "&"Attn:...."

I believe I can figure out the formatting of the printer on that end of things but where I am struggling is going from selecting the information from the ListBox and then using some sort of statement to go from the ListBox to:

-finding the information to print within the sheet (Name, attn, address, city, state, zip)
-continue down the ListBox until the next selected item
-repeat the process for each selected item within the ListBox.

Thanks for your help!

Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

My Aswer Is This

Well-known Member
Jul 5, 2014
Office Version
We would need to know several things if you want to use Excel Userform
1. The name of the Userform Listbox
2. The name of the sheet where you have all your Data
3. The name of the sheet you plan to print
4. The Ranges on the sheet where each value need to go.
5. And then see the script you have now for printing the sheets one at a time.
6. If you have no script now try using the Macro Recorder to print your sheet and then show us the Macro recorder script.

For example you need to say:
Address from Sheet("Master" column "C" need to go into Range ("G5") of the sheet named "PrintMe"


MrExcel MVP
Jan 15, 2007
One way to get the information would be to load it into the ListBox when the user form starts up with code like.
And then retrieve the information from the selected items with code like the command button code

Private Sub UserForm_Initialize()
    ListBox1.MultiSelect = fmMultiSelectMulti
    With Sheets("Sheet1").Range("A:A")
        With Range(.Cells(Rows.Count, 1).End(xlUp), .Range("F1"))
            ListBox1.ColumnCount = .Columns.Count
            ListBox1.List = .Value
            ListBox1.ColumnWidths = ";0;0;0;0;0"
        End With
    End With
End Sub

Private Sub CommandButton1_Click()
    Dim i As Long, j As Long
    Dim strLabel As String
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                strLabel = vbNullString
                For j = 0 To .ColumnCount - 1
                    strLabel = strLabel & vbCr & .List(i, j)
                Next j
                strLabel = Mid(strLabel, 2)
                ' for demo purpose only
                If MsgBox(strLabel, vbYesNo) = vbNo Then Exit Sub
            End If
        Next i
    End With
End Sub

Forum statistics

Latest member

This Week's Hot Topics