Macro to save to a single PDF

macropod · #1 11-17-2013, 02:39 AM

Hi LukeExcelKid,

Please be patient - some of us have lives to live outside of answering questions on the net.

If your Mac has a PDF 'printer', try something based on:

Code:

Private Sub CommandButton1_Click()
Dim wkSht As Worksheet, Ctrl As Control, i As Long, ArrShts()
With ActiveWorkbook
  ReDim Preserve ArrShts(0)
  Set wkSht = .ActiveSheet: i = -1
  For Each Ctrl In Me.Controls
    With Ctrl
      If .Name Like "CheckBox#*" Then
        If .Value = True Then
          i = i + 1
          ReDim Preserve ArrShts(i)
          ArrShts(i) = .Caption
        End If
      End If
    End With
  Next
  If UBound(ArrShts()) = 0 Then GoTo NoPrint
  .Sheets(ArrShts).Select
  Application.Dialogs(xlDialogPrint).Show Arg1:=1, Arg4:=1, Arg5:=False, Arg6:=True, Arg7:=1
  wkSht.Activate
End With
NoPrint:
Unload UserForm1
End Sub
 
Private Sub UserForm_Initialize()
CheckBox1.Caption = "Jan"
CheckBox2.Caption = "Feb"
CheckBox3.Caption = "Mar"
CheckBox4.Caption = "Apr"
CheckBox5.Caption = "May"
CheckBox6.Caption = "Jun"
CheckBox7.Caption = "Jul"
CheckBox8.Caption = "Aug"
CheckBox9.Caption = "Sep"
CheckBox10.Caption = "Oct"
CheckBox11.Caption = "Nov"
CheckBox12.Caption = "Dec"
CheckBox13.Caption = "Jan (2)"
CheckBox14.Caption = "Feb (2)"
CheckBox15.Caption = "Mar (2)"
CheckBox16.Caption = "Apr (2)"
CheckBox17.Caption = "May (2)"
CheckBox18.Caption = "Jun (2)"
CheckBox19.Caption = "Jul (2)"
CheckBox20.Caption = "Aug (2)"
CheckBox21.Caption = "Sep (2)"
CheckBox22.Caption = "Oct (2)"
CheckBox23.Caption = "Nov (2)"
CheckBox24.Caption = "Dec (2)"
End Sub

Note the elimination of your redundant 'Sheets().Name' expressions for the captions.

LukeExcelKid · #2 11-17-2013, 06:57 AM

Hi macropod,

Thank you for your quick reply.

This works well! The one thing I noticed is that it doesn't work if you only select one checkbox (one worksheet) to save/print to PDF. It works for (2) or more worksheets. Is there an adjustment to the code to allow a user to save/print one sheet if selected?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to save as pdf with ability to choose save as folder	rvessio	Word VBA	4	07-25-2016 12:37 PM
Change single character in PPT to another font macro	rtwwpad	PowerPoint	1	01-19-2013 05:08 PM
Macro to save as PDF but with a different name	shabbaranks	Word VBA	2	05-20-2011 01:02 AM
Macro to Save Help	clarkson001	Word	0	02-14-2011 06:41 AM
Macro Won't Save	lou0915	Word VBA	2	10-17-2009 08:13 PM