help! create Print Macro or Button

Hi Jeannie,

Maybe you could adapt something like this ...


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> JeanniePrints()
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
        <SPAN style="color:#00007F">With</SPAN> ws
            .PrintOut Copies:=1, Collate:=<SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ws
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hi, Jeannie, and welcome to the board.

Did you know that you can select multiple worksheets at the same time (shift-select the first tab of multiple sheets, and shift-select the last tab in the group for a continuous group of sheets OR cltrl-select individual sheets, when you want a discontiguous group), and when you hit the print button, all sheets print in order.

This will be useful for you to know in any case, whether you still decide you want or need a macro or not.

Chris

Heya Jeannie & Welcome to the Board!

PM me your e-mail addres and I'll send you a workbook that has a button to call a macro routine that allows you to select individual worksheets to print or all of them.

Smitty

(Heya Zack!)

EDIT: The following code will bring up a temporary dialog box with a check box next to each sheet that can be printed. To use it, right click on the worksheet tab where you want the button and select View Code. Paste the code in the window that opens. Hit ALT+Q to exit VBA and display the Control Toolbox (View-->Toolbars). Draw a Command Button on your sheet, name it Print or something like that, exit design mode and click the button to call the code.

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> TopPos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> SheetCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> PrintDlg <SPAN style="color:#00007F">As</SPAN> DialogSheet
    <SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> cb <SPAN style="color:#00007F">As</SPAN> CheckBox
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#007F00">'   Check for protected workbook</SPAN>
    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.ProtectStructure <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Workbook is protected.", vbCritical
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#007F00">'   Add a temporary dialog sheet</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveSheet
    <SPAN style="color:#00007F">Set</SPAN> PrintDlg = ActiveWorkbook.DialogSheets.Add

    SheetCount = 0

<SPAN style="color:#007F00">'   Add the checkboxes</SPAN>
    TopPos = 40
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ActiveWorkbook.Worksheets.Count
        <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveWorkbook.Worksheets(i)
<SPAN style="color:#007F00">'       Skip empty sheets and hidden sheets</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Application.CountA(CurrentSheet.Cells) <> 0 And _
            CurrentSheet.Visible <SPAN style="color:#00007F">Then</SPAN>
            SheetCount = SheetCount + 1
            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
                PrintDlg.CheckBoxes(SheetCount).Text = _
                    CurrentSheet.Name
            TopPos = TopPos + 13
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i

<SPAN style="color:#007F00">'   Move the OK and Cancel buttons</SPAN>
    PrintDlg.Buttons.Left = 240

<SPAN style="color:#007F00">'   Set dialog height, width, and caption</SPAN>
    <SPAN style="color:#00007F">With</SPAN> PrintDlg.DialogFrame
        .Height = Application.Max _
            (68, PrintDlg.DialogFrame.Top + TopPos - 34)
        .Width = 230
        .Caption = "Select sheets to print"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#007F00">'   Change tab order of OK and Cancel buttons</SPAN>
<SPAN style="color:#007F00">'   so the 1st option button will have the focus</SPAN>
    PrintDlg.Buttons("Button 2").BringToFront
    PrintDlg.Buttons("Button 3").BringToFront

<SPAN style="color:#007F00">'   Display the dialog box</SPAN>
    CurrentSheet.Activate
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">If</SPAN> SheetCount <> 0 <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#007F00">'Print as one print job (continuous page numbers)</SPAN>
    <SPAN style="color:#007F00">'        If PrintDlg.Show Then</SPAN>
    <SPAN style="color:#007F00">'            For Each cb In PrintDlg.CheckBoxes</SPAN>
    <SPAN style="color:#007F00">'                If cb.Value = xlOn Then</SPAN>
    <SPAN style="color:#007F00">'                    Worksheets(cb.Caption).Select Replace:=False</SPAN>
    <SPAN style="color:#007F00">'                End If</SPAN>
    <SPAN style="color:#007F00">'            Next cb</SPAN>
    <SPAN style="color:#007F00">'            ActiveWindow.SelectedSheets.PrintOut copies:=1</SPAN>
    <SPAN style="color:#007F00">'            ActiveSheet.Select</SPAN>
    <SPAN style="color:#007F00">'        End If</SPAN>
    <SPAN style="color:#007F00">'Print as separate print jobs</SPAN>
        <SPAN style="color:#00007F">If</SPAN> PrintDlg.Show <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cb <SPAN style="color:#00007F">In</SPAN> PrintDlg.CheckBoxes
                <SPAN style="color:#00007F">If</SPAN> cb.Value = xlOn <SPAN style="color:#00007F">Then</SPAN>
                    Worksheets(cb.Caption).Activate
                    ActiveSheet.PrintOut
<SPAN style="color:#007F00">'                   ActiveSheet.PrintPreview 'for debugging</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> cb
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        MsgBox "All worksheets are empty."
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#007F00">'   Delete temporary dialog sheet (without a warning)</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    PrintDlg.Delete

<SPAN style="color:#007F00">'   Reactivate original sheet</SPAN>
    CurrentSheet.Activate
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Dear Firefytr, Blue Hornet, Penny Saver -

Thanks for your great suggestions. They work great.

Though learning VBA is hard.

Jeannie

Though learning VBA is hard.

Click to expand...

See VBA Certification It's free Excel (Word & Access coming soon) VBA training put on by the great folks at VBA Express, a site that firefyter (Zack) helps run. It's well worth checking out.

Smitty

Smitty: It is NOT free. I don't know where you got that idea...

But it IS worth checking out.
DRJ is doing a terrific job...

Smitty: It is NOT free.

Click to expand...

Sorry, being a new Daddy also means that I'm a bonehead. (More than usual anyway!) All of your, Zack & and Jacob's (and others') work oughtta be paid for!

Apologies!

Smitty

LOL
No probs, dude.