Help with Print Macro

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
534
I have a print macro i found on the internet and would like to see if it can be changed to show the tab name being printed instead of the workbook name. This would be helpful when printing multiple tabs at the same time, so I would know which tab is printing. They print in order selected but when selecting many tabs its hard to remember the names and order.

VBA Code:
Sub SelectSheets()

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
Dim swch As Boolean
swch = False
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
'Test if the printer's been re-set - if not show the print dialog, select the correct printer, then print the sheet. If it has, just printout.
If swch = False Then Application.Dialogs(xlDialogPrint).Show: swch = True: GoTo skip1
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
skip1: 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
swch = False
End Sub

Thank you
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
changed to show the tab name being printed instead of the workbook name.

I don't see in this code where anything shows the workbook name. Can you be more specific about where this appears?
 

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
534
It happens when the print box appears and I then select Adobe. then the save as box appears with the workbook name, and I change to the tab name
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
I see the problem. You are printing the sheet using

VBA Code:
ActiveSheet.PrintOut

This uses whatever is the selected printer. Once the sheet is sent to the printer, Excel has no control over what happens. You have selected Adobe PDF Writer (or something like that) as your printer. At that point Adobe decides what to name your file. Excel has no control over it.

You need to change your code to use Excel's built-in PDF writer, where you can control this. Instead of the line of code above, use this:

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

By the way, I strongly suggest you use indentation to show the logic structure of your code. It would make it much easier to read.
 

Mldeuser

Well-known Member
Joined
Dec 27, 2008
Messages
534
I did as noted above and it still saves as the workbook name and not the tab name. Also it only saves the first tab selected, all others are ignored.

Thank you
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
You have a loop that is selecting only one sheet at a time, and printing it. I am not sure what you mean by "first tab selected." The code I provided exports it as a PDF instead of printing it to the default printer. It also names the file based on the sheet. I don't see how it's possible that it is using the workbook name to name the PDF file.

Please show the revised code that is not working. (BTW the readability of the code could be enhanced by indentation to show the structure.)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,867
Messages
5,638,757
Members
417,051
Latest member
arthur_w

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
Top