Excel -- Print Selected Items in Order Form

Thanks to Dave Peterson, who wrote this technique.

Add an "X" to items in a worksheet list, and the following code will print each marked item in a worksheet order form.

Values from the worksheet list, starting in Column B and moving right, are copied to the order form, into the addresses you've specified in this line:

myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16")

Change those references to match your order form.

Near the start of the code, change the worksheet names to match your worksheet names:

 

FormPrint.zip

 

 

Set FormWks = Worksheets("Order Form")
Set DataWks = Worksheets("Orders")

 

 

 

The "X" marks are cleared at the end of the code, to prepare for the next print session, and a message is displayed, indicating how many orders were printed.

After testing, you can change Preview to False in this line:

FormWks.PrintOut Preview:=True

and the forms will print, instead of showing a preview.

Note: When you copy the code to your workbook, include the following line at the top of the code module:

Option Base 0

If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm

The zipped sample workbook can be downloaded here:
FormPrint.zip

  

  

Option Base 0
Sub PrintUsingDatabase()
    Dim FormWks As Worksheet
    Dim DataWks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    Dim iCtr As Long
    Dim myAddresses As Variant
    Dim lOrders As Long

    Set FormWks = Worksheets("Order Form")
    Set DataWks = Worksheets("Orders")

    myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16")

    With DataWks
        'first row of data to last row of data in column B
        Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
    End With

    For Each myCell In myRng.Cells
        With myCell
            If IsEmpty(.Offset(0, -1)) Then
                'if the row is not marked, do nothing
            Else
                .Offset(0, -1).ClearContents 'clear mark for the next time
                For iCtr = LBound(myAddresses) To UBound(myAddresses)
                    FormWks.Range(myAddresses(iCtr)).Value _
                        = myCell.Offset(0, iCtr).Value
                Next iCtr
                Application.Calculate 'just in case
                 'after testing, change to Preview to False to Print
                FormWks.PrintOut Preview:=True
                lOrders = lOrders + 1
            End If
        End With
    Next myCell
    
    MsgBox lOrders & " orders were printed."

End Sub

 

1. Forms -- Survey Form with Option Buttons
2. Forms -- Worksheet Data Entry Form
3. Forms -- Print Selected Items in Order Form

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:40 PM