Print Multiple Items from Listbox

[rockchalk33]

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

 

[alansidman]

Why not use the mail merge wizard in Word to draw your names and addresses from your excel spreadsheet.

https://www.google.com/search?q=Exc...how+to+print+envelopes+from+excel+spreadsheet

 

[My Aswer Is This]

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]

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