Print only certain worksheets?--Excel experts pls help

cjones88

New Member
Joined
Mar 14, 2002
Messages
23
I want to make a popup or some type of inquiry menu where it asks the user to specify which worksheets in the workbook to print. I have a data entry page, and four printable worksheets. Sometimes I only want to print two of the worksheets, but not the data page or the other two. Any way to do this besides printing the entire workbook or one worksheet at a time? It would be ideal if a popup menu came up with checkboxes for all of the available worksheets and the user could check off which ones to print.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi,

If you wanted to print more than one sheets, there are alternative ways to do that.

First, select one of the sheets you wanted to print, then press & hold down on "Ctrl" key, and then select the other sheets you wanted to print. After all the sheets you wanted to print are highlighted, then press print, it should print all the highlighted sheets at one go.

Otherwise, if you really want a checkbox / listbox menu, you can set that up, and then add the following line of code after the checkboxes are ticked (similiar coding):

Sheets(Array("sheet1", "sheet2")).Print
 
Upvote 0
Thanks for the start. However, I know very little about VB. I know how to make Macros and assign them to buttons, but as far as lists and checkboxes, I know nothing. Could you instruct me a little further on how I could set up such a box that contains the ability to check off each sheet that I want printed? If not, is there a website the teaches the basics of these boxes in Excel? Thanks!
 
Upvote 0
Hi,

Hope the following steps might get you started:

STEP1: Select Viscal Basic Editor under tools/macros, that will bring you to the code editor.
STEP2: Select Insert/userforms, that will insert an userform for you in this workbook.
STEP3: You might see there is a toolbox popup menu after you have inserted the userform, choose checkbox, and click on the userform, that would insert a checkbox on your form. don't have to change the name of the checkboxes yet, insert 4 of them
STEP4: Double click on the userform (not on the checkboxes), that will bring you to the coding behind this userform.
STEP5: at the top of the coding page, there are 2 options, select "userform1" in the left option, and select "initialise" for the right. That will setup a private sub for the initialisation of this userform.
STEP6: Insert the following codes(similiar codes only) into the private sub setup in the SETP5:

CheckBox1.Caption = Sheets("Sheet1").Name
CheckBox2.Caption = Sheets("Sheet2").Name
CheckBox3.Caption = Sheets("Sheet3").Name
CheckBox4.Caption = Sheets("Sheet4").Name

STEP7: Go back to userform, and insert a button, and double click on the button, and then insert the following codes:
If CheckBox1.Value = True Then Sheets("Sheet1").PrintOut Copies:=1
If CheckBox2.Value = True Then Sheets("Sheet2").PrintOut Copies:=1
If CheckBox3.Value = True Then Sheets("Sheet3").PrintOut Copies:=1
If CheckBox4.Value = True Then Sheets("Sheet4").PrintOut Copies:=1
Unload UserForm1

STEP8: setup your macro button on your sheet to have the following line, just to activate you userform:

userform1.show

Hope the above helps.
 
Upvote 0
You are a lifesaver. Thank you so much for this. The formula works perfectly, and I know how to customize it for my particular application.

I am very interested in learning to write and be efficient in VB. I work for a major brokerage firm that has terrible software, so I am left creating my own. Any good suggestions on starter books/websites?
 
Upvote 0
I found step by step Excel VB my microsoft a good starting point, it shows you how to use VB plus there is a bit there about customizing applications and user forms etc.

I am in a similar situation to yourself, our department has complicated spreadsheets which are in a mess simply as the people who set them up were clueless about excel and I am currently transforming them to be useful and flexible and more powerful etc. Hard work but bags of fun I must admit.
 
Upvote 0
This works like a champ! I have one question however. Is there a way to ammend this script allow for selection of installed printers? Currently this just sends to the default printer.
 
Upvote 0
Hi,

This seems exactly what i am looking for.

Is it possible to specify printing more than 1 worksheet?

i.e

If CheckBox1.Value = True Then Sheets("Sheet1, Sheet9, Sheet13").PrintOut Copies:=1

thanks
 
Upvote 0
AWESOME!

Thanks so much! This works fantastic! Your detailed step-by-step instructions were very easy to follow and simple to do. This will come in very useful! :biggrin: :biggrin: :biggrin:
 
Upvote 0
I searched for messages on printing select worksheets and found this one, which is exactly what I am trying to do. I have followed the steps above, and it isn't working. I am not so good with VBA, and have probably screwed up something simple.

I am pretty sure I made it through the first 6 steps. I replaced "Sheet1" etc with my actual sheet names. And, I replaced the word "Name" with what I wanted that option to be called - is this correct? I copied the text from step 7 verbatim, and only changed "Sheet1" etc with my sheet names. I think I have screwed up step 8...I created a new macro (see below), created a button and assigned this macro to the button. Nothin is happening.

Sub Print_Sheets()
'
' Print_Sheets Macro
' Macro recorded 4/4/2006 by Sarah C. Rosenberg
' UserForm1.Show
End Sub

Thoughts? Suggestions??
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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