Macro for printing sheets that are selected on a checkbox

X

xoxleahxox

New Member
Joined
Dec 19, 2007
Messages
2
Hi There,

What I want to be able to do is have a checkbox with the list of sheets I have, for example sheets Jan, Feb, Mar, Apr and then have a button at the bottom that has Print Selected. So that you can select which sheets out of the list (By using the checkboxes) you want to print, then hit print selected and it will only print the ones you have selected.

Thank you,

Leah
 
Sort by date Sort by votes
Leah,

I use a userform with a listbox that contains all the visible sheets. The form contains the following:

  • A button called btn_Print
  • A button called btn_Cancel
  • A checkbox called chk_Select_all
  • A listbox called Lst_sheets

Here's the code:
Code: 
Private Sub chk_Select_all_Click()
Dim iloop As Integer

For iloop = 1 To Lst_sheets.ListCount
 Lst_sheets.Selected(iloop - 1) = chk_Select_all.Value
Next
End Sub

Private Sub btn_Cancel_Click()
    Unload Me
End Sub

Private Sub btn_Print_Click()
Application.EnableEvents = False
Dim iloop As Integer
For iloop = 1 To Lst_sheets.ListCount
 If Lst_sheets.Selected(iloop - 1) = True Then
 Application.ScreenUpdating = False
   Sheets(Lst_sheets.List(iloop - 1, 0)).PrintOut
   Lst_sheets.Selected(iloop - 1) = False
 End If
 Next
 
 Unload Me
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub UserForm_Initialize()
Dim sSheet


    For Each sSheet In Sheets
     If sSheet.Visible <> xlSheetVisible Then GoTo Next_sheet
        If sSheet.Type = 3 Then 'Chart sheet
            Lst_sheets.AddItem sSheet.Name
        ElseIf WorksheetFunction.CountA(sSheet.Cells) > 0 Then
            Lst_sheets.AddItem sSheet.Name
        End If
Next_sheet:
    Next sSheet

Application.EnableEvents = True

End Sub
 
Upvote 0
Hi!

Another way to do this is to create a SHEET called ToPrint or something in your workbook. List the sheet names of your workbook that you want to give the option to print in Column A. In column B, tell the user to put type a "1" if you want to print or a "0" if you don't want to print.

Then write code that goes through what's entered in those cells and print accordingly. You can get the code that prints each sheet by TOOLS/MACRO/RECORD NEW MACRO and then print a worksheet. Then choose menu TOOLS/MACRO/STOP RECORDING.

You now have the code to print the worksheets, then you just need the code to loop through all the user indicated choices of what worksheet to print.

I hope this answer wasn't too loquacious or circuitous.

If this sounds like what you need, this can be done very quickly. Let me know if this sounds good to you and I can post something (that is if someone doesn't beat me to it).
 
Last edited:
Upvote 0
Hi There,

Yes, that does sound like what I am looking for, it may be a little easier for me as I am new to using macros (Please forgive me if I ask questions, I am a fast learner though) :)

THANKS!

Leah
 
Upvote 0
xoxleahxox said:
Hi There,

Yes, that does sound like what I am looking for, it may be a little easier for me as I am new to using macros (Please forgive me if I ask questions, I am a fast learner though) :)

THANKS!

Leah
Click to expand...

Not sure if you were replying to me or gwkenny, but if you pm me with your email address, I can send you a template with the userform built in.
 
Upvote 0
Please ignore the crappy formatting :D

Here is a view of the sheet and code behind it.

Besides the ugly look (which should be very easy for you to sharpen up) it is dynamic per se. If you have more sheets, just type the sheet names accordingly in column B. Leave no blank rows between sheet names in column B, as when the code encounters a blank row in column B, it stops working.
Book1
ABCD
1
2
3Please indicate sheets to print (1 for yes, 0 for no)
4Sheet21
5Sheet30
6
ToPrint


I have one range name on this sheet. It is prnSheets. The address is B3. So please define B3 as the range name prnSheets.

Here is the code that goes into a module in that workbook

*** begin module code ***<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> DOPrintSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> prnSheets<SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wkshtTemp<SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> prnSheets = Range("prnSheets").Offset(1, 0)<br>    <br>    <SPAN style="color:#00007F">While</SPAN> prnSheets<> ""<br>        <SPAN style="color:#00007F">If</SPAN> prnSheets.Offset(, 1) = 1<SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">Resume</SPAN><SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wkshtTemp = Worksheets(prnSheets.Text)<br>            <SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <br>            <SPAN style="color:#00007F">If</SPAN> wkshtTemp<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "The following Sheet was indicated to print, but not found in the workbook:  " & prnSheets & "." & vbCr & "Program is halting"<br>                <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>            wkshtTemp.PrintOut<br>            <br>        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">Set</SPAN> wkshtTemp =<SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> prnSheets = prnSheets.Offset(1, 0)<br>    <SPAN style="color:#00007F">Wend</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><br></FONT>

*** end module code ***

Enjoy
 
Upvote 0
gwkenny said:
Please ignore the crappy formatting :D

Here is a view of the sheet and code behind it.

Besides the ugly look (which should be very easy for you to sharpen up) it is dynamic per se. If you have more sheets, just type the sheet names accordingly in column B. Leave no blank rows between sheet names in column B, as when the code encounters a blank row in column B, it stops working.

******** ******************** ************************************************************************><center>[TABLE="align: center"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0c266b]#0c266b[/URL] , colspan: 5"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD="align: left"]Microsoft Excel - Book1[/TD]
[TD="align: right"]___Running: 11.0 : OS = Windows XP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] , colspan: 5"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD](F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout[/TD]
[TD="align: center"]<form name="formCb605117"><input *******="window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);" type="button" value="Copy Formula" name="btCb942116"></form>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 5"]

<tbody>
[TD="bgcolor: white"]<select onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name="sltNb447362"><option value="" selected="">A1</option></select>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] , align: right"] = [/TD]
[TD="bgcolor: white, align: left"]<input size="80" name="txbFb150492">[/TD]

</tbody>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>A</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>B</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>C</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>D</center>[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>1</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>2</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>3</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: left"]Please indicate sheets to print (1 for yes, 0 for no)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>4</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: left"]Sheet2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>5</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: left"]Sheet3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] "]<center>6</center>[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=d4d0c8]#d4d0c8[/URL] , colspan: 5"][TABLE="width: 100%, align: left"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: left"]ToPrint[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

I have one range name on this sheet. It is prnSheets. The address is B3. So please define B3 as the range name prnSheets.

Here is the code that goes into a module in that workbook

*** begin module code ***

Option Explicit

Sub DOPrintSheets()
Dim prnSheets As Range
Dim wkshtTemp As Worksheet

Set prnSheets = Range("prnSheets").Offset(1, 0)

While prnSheets <> ""
If prnSheets.Offset(, 1) = 1 Then
On Error Resume Next
Set wkshtTemp = Worksheets(prnSheets.Text)
On Error GoTo 0

If wkshtTemp Is Nothing Then
MsgBox "The following Sheet was indicated to print, but not found in the workbook: " & prnSheets & "." & vbCr & "Program is halting"
Exit Sub
Else
End If
wkshtTemp.PrintOut

End If

Set wkshtTemp = Nothing
Set prnSheets = prnSheets.Offset(1, 0)
Wend

End Sub


*** end module code ***

Enjoy
Click to expand...

Hi Guys,

I am totally new to the fourm - and VBA in general and never tried any programming before.

By googling I found this very helpful thread and VBA code - thanks for that!

Is the anyone who can help with re-writing the above code so that instead of printing each sheet one-by-one, it combine the selected sheets to one PDF file?

To give an example, I have this code from another workbook that use "savePDF" instead of printing:

Thanks - hoping you can help :)

*****************

Sub SavePDF()
'
' SavePDF Macro
'


'
Dim fname As String

With ActiveSheet

fname = .Range("b118").Value

Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

End With

Sheets("Configurator").Select

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Complex Print Button
Replies
2
Views
315
kevin9999
K
I
  • Solved
Print sheets based on checkbox
Replies
3
Views
2K
John_w
J
S
  • Solved
copy data from userform to multiple sheets based on checkbox selection
Replies
4
Views
329
Starchaser
S
Dan Wilson
  • Question Question
Help with a short Macro
2
Replies
11
Views
580
Peter_SSs
Peter_SSs
K
  • Question Question
Printing Multiple "Named Ranges" to PDF
Replies
3
Views
340
jolivanes
jolivanes

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