Print Multiple Items from Listbox

rockchalk33

Board Regular
Joined
Jan 12, 2016
Messages
103
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!

Devin
 
Last edited:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
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"
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,963
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

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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,902
Messages
5,411,118
Members
403,342
Latest member
faizanhamied

This Week's Hot Topics

Top