Urgent - print macro button

[jin]

Hi,

I need to know how to make a box that says "print" which will print out pages in my excel file... What I am picturing is this:

Tab 1 Y
Tab 2 Y
Tab 3 N
... etc.

Making a table as the one above, and depending on whether I input "Y" or "N", it will print that tab...

i've seen this in some financial models and would like to incorporate it into my model. If you could offer some detailed help, it would be greatly appreciated. Please note that I am a complete beginner and would need to be taken step-by-step.

Thanks again.

 

[Smitty]

Welcome to the Board!

Here's a great Print routine by John Walkenbach that will list all of your sheets in a list box and allow you to select the ones you want to print:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SelectSheets()<br>    <SPAN style="color:#007F00">'   John Walkenbach</SPAN><br>    <SPAN style="color:#007F00">'   www.j-walk.com</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> TopPos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> SheetCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> PrintDlg <SPAN style="color:#00007F">As</SPAN> DialogSheet<br>    <SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> cb <SPAN style="color:#00007F">As</SPAN> CheckBox<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#007F00">'   Check for protected workbook</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.ProtectStructure <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Workbook is protected.", vbCritical<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#007F00">'   Add a temporary dialog sheet</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveSheet<br>    <SPAN style="color:#00007F">Set</SPAN> PrintDlg = ActiveWorkbook.DialogSheets.Add<br><br>    SheetCount = 0<br><br><SPAN style="color:#007F00">'   Add the checkboxes</SPAN><br>    TopPos = 40<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> ActiveWorkbook.Worksheets.Count<br>        <SPAN style="color:#00007F">Set</SPAN> CurrentSheet = ActiveWorkbook.Worksheets(i)<br><SPAN style="color:#007F00">'       Skip empty sheets and hidden sheets</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Application.CountA(CurrentSheet.Cells) <> 0 And _<br>            CurrentSheet.Visible <SPAN style="color:#00007F">Then</SPAN><br>            SheetCount = SheetCount + 1<br>            PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5<br>                PrintDlg.CheckBoxes(SheetCount).Text = _<br>                    CurrentSheet.Name<br>            TopPos = TopPos + 13<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br><SPAN style="color:#007F00">'   Move the OK and Cancel buttons</SPAN><br>    PrintDlg.Buttons.Left = 240<br><br><SPAN style="color:#007F00">'   Set dialog height, width, and caption</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> PrintDlg.DialogFrame<br>        .Height = Application.Max _<br>            (68, PrintDlg.DialogFrame.Top + TopPos - 34)<br>        .Width = 230<br>        .Caption = "Select sheets to print"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#007F00">'   Change tab order of OK and Cancel buttons</SPAN><br><SPAN style="color:#007F00">'   so the 1st option button will have the focus</SPAN><br>    PrintDlg.Buttons("Button 2").BringToFront<br>    PrintDlg.Buttons("Button 3").BringToFront<br><br><SPAN style="color:#007F00">'   Display the dialog box</SPAN><br>    CurrentSheet.Activate<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> SheetCount <> 0 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> PrintDlg.Show <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cb <SPAN style="color:#00007F">In</SPAN> PrintDlg.CheckBoxes<br>                <SPAN style="color:#00007F">If</SPAN> cb.Value = xlOn <SPAN style="color:#00007F">Then</SPAN><br>                    Worksheets(cb.Caption).Activate<br>                    ActiveSheet.PrintOut<br><SPAN style="color:#007F00">'                   ActiveSheet.PrintPreview 'for debugging</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> cb<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "All worksheets are empty."<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#007F00">'   Delete temporary dialog sheet (without a warning)</SPAN><br>    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    PrintDlg.Delete<br><br><SPAN style="color:#007F00">'   Reactivate original sheet</SPAN><br>    CurrentSheet.Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope that helps,

 

[jin]

Smitty,

thank you for your prompt reply -- as I mentioned in my previous post, i have no idea how to use macros... do i go to tools --> add macro and enter the coding?

i'm sorry once again for the trouble, but thank you for your time... you saved my life

 

[Smitty]

as I mentioned in my previous post, i have no idea how to use macros

Actually you didn't mention that, but no worries.

In Excel hit Alt+F11, which will open the VBE (Visual Basic Editor). Then goto Insert-->Module. Paste the code I posted in the new window that opens on the right, then you can exit back to Excel with Alt+Q.

I'd recommend going to View-->Toolbars-->Forms and drawing a button on your sheet. When you're done creating it, you'll get a prompt to assign a macro to it (or you can right-click it). Assign the "Select Sheets" code to it and give it a go.

Post back if you have any other questions, and if I can't get to them, one of us will!

 

[jin]

Smitty,

Thanks again for your help. I copied and pasted the macro on to the VBA module, and closed out of it. After I made the button, it says "macro name"? which name do I need to assign to it?

Sorry for the trouble --

Thanks again,

Jin

 

[jin]

Also, is there any way that we change the coding to make it so that after I hit print and OK, the screen automatically goes to the last tab?

Thanks so much for your help.

 

[Smitty]

After I made the button, it says "macro name"? which name do I need to assign to it?

Assign the "Select Sheets" code to it

As for going to the last sheet in the wb you can append:

Sheets(Sheets.Count).Select

To the end of the code.

 

[jin]

It works!!!

I meant to say... I need the coding to NOT make the screen go to the last tab after I hit "print" or "cancel" on the macro... Would I enter this code under neath or above the "End Sub" line?

 

[Smitty]

John's code doesn't activate any sheets, so you should be left where you started.

As for where to put the code, it would go before the End Sub line, which is the End-ing statement.

 

[jin]

whenever i click on the print button and "ok" or "cancel", the file takes me to my last tab....

is there anyway i can fix this?

thanks for the reference point to putting the code before 'end-ing' haha. makes sense...