Urgent - print macro button

jin · Oct 16, 2008

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 · Oct 16, 2008

OK,

I tweaked the code a bit to return to the starting sheet:

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

My bad for not catching that, I haven't used the code in years.

jin · Oct 16, 2008

works perfectly. thanks so much...

jin · Oct 16, 2008

I have a new question that I need help with!

In a new file, I am trying to find a macro to automate the print process (like the macro given above) under certain categories... eg...

1 computer
1 monitor
1 phone
2 can
2 fax

And a macro that will distinguish the first column into "1" and "2", and I can click a button and print all the "1" lines...

Can you help me out please? Thank you, as always..

KGee · Jan 2, 2009

Smitty said:
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:

Sub SelectSheets()
' John Walkenbach
' www.j-walk.com
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
CurrentSheet.Activate
End Sub

Hope that helps,

I was just looking for something exactly like this. Works great. Thanks!

Smitty · Jan 2, 2009

jin said:
I have a new question that I need help with!

In a new file, I am trying to find a macro to automate the print process (like the macro given above) under certain categories... eg...

1 computer
1 monitor
1 phone
2 can
2 fax

And a macro that will distinguish the first column into "1" and "2", and I can click a button and print all the "1" lines...

Can you help me out please? Thank you, as always..

Sorry I missed your reply (must have been out of town)...You can do what you want with Data-->Auto Filter. Filter on 1 and print, then repeat for 2. It's easily recordable.

charllie · Feb 12, 2009

Hi Smitty,

Or anyone else out there.

This is a brilliant code for printing making life a lot easier. I have two questions to sdaot it for me.

I would like to know if there is a way to make it print graphs that are in a worksheet.

I have a workbook with approx 8 graphs, one graph per page. However the checkboxes do not include any of these graph pages.

Secondly, is there a way that it would allow us to choose how many copies to print?

Thanks All

Charllie

gadgetmeister · Jun 3, 2009

Smitty said:
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:

Smitty,

Much thanks to you and John Walkenbach for this script, very useful indeed ......

ray:

Rather than the user have to chose which sheets to print, can it be tweaked to automatically only print certain sheets based on whether or not specific cells on the 1st sheet contains results?
i.e. Sheet 1 has a series of 10 cells that contain results from the tables on the other 5 sheets, but I don't want to print sheets 2,3,4,5 or 6 if the results in the table on sheet 1 are zero.
As I'm just having fun with the algebra side of Excel at the moment, any guidance on this would be gratefully appreciated.

Smitty · Jun 3, 2009

You could do something along these lines:

Code:

Dim ws as Worksheet
  For Each ws in ActiveWorkbook.Worksheets
     If ws.Range("A1").Value <> 0 Then ws.PrintOut
  Next ws
End SUb

Mldeuser · Jun 3, 2009

Can the ability to select all sheets be added to this.

Thanks

Smitty · Jun 3, 2009

Mldeuser said:
Can the ability to select all sheets be added to this.

Thanks

Code:

Dim ws as Worksheet
  For Each ws in ActiveWorkbook.Worksheets
     ws.PrintOut
  Next ws
End Sub

HTH

Urgent - print macro button

jin

New Member

Smitty

Legend

Excel Facts

jin

New Member

jin

New Member

KGee

Well-known Member

Smitty

Legend

charllie

Well-known Member

gadgetmeister

New Member

Smitty

Legend

Mldeuser

Well-known Member

Smitty

Legend

Similar threads

Forum statistics

Share this page

Urgent - print macro button

New Member

Legend

Excel Facts

New Member

New Member

Well-known Member

Legend

Well-known Member

New Member

Legend

Well-known Member

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock