Print only certain worksheets?--Excel experts pls help

cjones88 · Mar 21st, 2002, 01:49 PM

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.

BabyTiger · Mar 21st, 2002, 02:14 PM

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

cjones88 · Mar 21st, 2002, 02:17 PM

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!

BabyTiger · Mar 21st, 2002, 03:37 PM

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.

cjones88 · Mar 22nd, 2002, 09:36 AM

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?

RET79 · Mar 22nd, 2002, 10:24 AM

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.

hibberts · Feb 14th, 2006, 05:07 PM

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.

jarrvo · Mar 9th, 2006, 12:08 PM

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

Tubig00 · Mar 11th, 2006, 03:43 PM

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!

sarahrosenberg · Apr 4th, 2006, 04:42 PM

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??

Mar 11th, 2006, 03:43 PM	#9
Tubig00 New Member Join Date: Mar 2006 Posts: 15	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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 01:49 PM	#1
cjones88 New Member Join Date: Mar 2002 Location: Atlanta Posts: 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.

Mar 21st, 2002, 02:14 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	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

Mar 21st, 2002, 02:17 PM	#3
cjones88 New Member Join Date: Mar 2002 Location: Atlanta Posts: 23	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!

Mar 21st, 2002, 03:37 PM	#4
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	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.

Mar 22nd, 2002, 09:36 AM	#5
cjones88 New Member Join Date: Mar 2002 Location: Atlanta Posts: 23	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?

Mar 22nd, 2002, 10:24 AM	#6
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	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.

Feb 14th, 2006, 05:07 PM	#7
hibberts New Member Join Date: Feb 2006 Posts: 9	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.

Mar 9th, 2006, 12:08 PM	#8
jarrvo New Member Join Date: Mar 2006 Posts: 1	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

Apr 4th, 2006, 04:42 PM	#10
sarahrosenberg Board Regular Join Date: Aug 2002 Location: Nashville, TN USA Posts: 170	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??