Urgent - print macro button

J

jin

New Member
Joined
Oct 16, 2008
Messages
8
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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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,
 
Upvote 0
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 :)
 
Upvote 0
as I mentioned in my previous post, i have no idea how to use macros
Click to expand...

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! :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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. ;)
 
Upvote 0
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... :LOL:
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Print Batching
Replies
7
Views
343
John_w
J
E
  • Solved
Create button to print a separate workbook without opening.
Replies
2
Views
243
EnviroBob
E
B
  • Solved
macro to print selection when there is data in rows
2 3
Replies
24
Views
1K
bh24524
B
I
  • Question
Make notes on excel generated invoice but when printing hide them
Replies
9
Views
251
ipbr21054
I
W
  • Question
Printing Sequential Barcodes without having to create the sequence in Excel
Replies
2
Views
457
Worthawiggles
W

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top