Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Macro to save to a single PDF Macro to save to a single PDF
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 11-16-2013, 03:48 PM
LukeExcelKid LukeExcelKid is offline Macro to save to a single PDF Mac OS X Macro to save to a single PDF Office for Mac 2011
Novice
Macro to save to a single PDF 		 
Join Date: Nov 2013
Posts: 3
LukeExcelKid is on a distinguished road
Default Macro to save to a single PDF
Hi!



I am new to VBE in Excel, so please bare with me FYI, I am a Mac Excel 2011 user.

I have set up a marco button that when clicked, a pop up displays listing all the active worksheets. The user selects the sheets he/she wants to print, then it prints to the default printer.

But I now want to set this up so it saves all selected worksheets to a SINGLE pdf file. Ideally, I would also like it to ask the user what file name he/she wants to save it as. And select a destination.

Below is my current code. Any advice would be greatly appreciated. Thank you!

Code:
Private Sub CheckBox2_Click()

End Sub

Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then Sheets("Jan").PrintOut Copies:=1
If CheckBox2.Value = True Then Sheets("Feb").PrintOut Copies:=1
If CheckBox3.Value = True Then Sheets("Mar").PrintOut Copies:=1
If CheckBox4.Value = True Then Sheets("Apr").PrintOut Copies:=1
If CheckBox5.Value = True Then Sheets("May").PrintOut Copies:=1
If CheckBox6.Value = True Then Sheets("Jun").PrintOut Copies:=1
If CheckBox7.Value = True Then Sheets("Jul").PrintOut Copies:=1
If CheckBox8.Value = True Then Sheets("Aug").PrintOut Copies:=1
If CheckBox9.Value = True Then Sheets("Sep").PrintOut Copies:=1
If CheckBox10.Value = True Then Sheets("Oct").PrintOut Copies:=1
If CheckBox11.Value = True Then Sheets("Nov").PrintOut Copies:=1
If CheckBox12.Value = True Then Sheets("Dec").PrintOut Copies:=1
If CheckBox13.Value = True Then Sheets("Jan (2)").PrintOut Copies:=1
If CheckBox14.Value = True Then Sheets("Feb (2)").PrintOut Copies:=1
If CheckBox15.Value = True Then Sheets("Mar (2)").PrintOut Copies:=1
If CheckBox16.Value = True Then Sheets("Apr (2)").PrintOut Copies:=1
If CheckBox17.Value = True Then Sheets("May (2)").PrintOut Copies:=1
If CheckBox18.Value = True Then Sheets("Jun (2)").PrintOut Copies:=1
If CheckBox19.Value = True Then Sheets("Jul (2)").PrintOut Copies:=1
If CheckBox20.Value = True Then Sheets("Aug (2)").PrintOut Copies:=1
If CheckBox21.Value = True Then Sheets("Sep (2)").PrintOut Copies:=1
If CheckBox22.Value = True Then Sheets("Oct (2)").PrintOut Copies:=1
If CheckBox23.Value = True Then Sheets("Nov (2)").PrintOut Copies:=1
If CheckBox24.Value = True Then Sheets("Dec (2)").PrintOut Copies:=1
Unload UserForm1

End Sub


Private Sub UserForm_Initialize()
CheckBox1.Caption = Sheets("Jan").Name
CheckBox2.Caption = Sheets("Feb").Name
CheckBox3.Caption = Sheets("Mar").Name
CheckBox4.Caption = Sheets("Apr").Name
CheckBox5.Caption = Sheets("May").Name
CheckBox6.Caption = Sheets("Jun").Name
CheckBox7.Caption = Sheets("Jul").Name
CheckBox8.Caption = Sheets("Aug").Name
CheckBox9.Caption = Sheets("Sep").Name
CheckBox10.Caption = Sheets("Oct").Name
CheckBox11.Caption = Sheets("Nov").Name
CheckBox12.Caption = Sheets("Dec").Name
CheckBox13.Caption = Sheets("Jan (2)").Name
CheckBox14.Caption = Sheets("Feb (2)").Name
CheckBox15.Caption = Sheets("Mar (2)").Name
CheckBox16.Caption = Sheets("Apr (2)").Name
CheckBox17.Caption = Sheets("May (2)").Name
CheckBox18.Caption = Sheets("Jun (2)").Name
CheckBox19.Caption = Sheets("Jul (2)").Name
CheckBox20.Caption = Sheets("Aug (2)").Name
CheckBox21.Caption = Sheets("Sep (2)").Name
CheckBox22.Caption = Sheets("Oct (2)").Name
CheckBox23.Caption = Sheets("Nov (2)").Name
CheckBox24.Caption = Sheets("Dec (2)").Name


End Sub
Last edited by LukeExcelKid; 11-16-2013 at 07:59 PM.
Reply With Quote
  #2  
Old 11-16-2013, 08:00 PM
LukeExcelKid LukeExcelKid is offline Macro to save to a single PDF Mac OS X Macro to save to a single PDF Office for Mac 2011
Novice
Macro to save to a single PDF 		 
Join Date: Nov 2013
Posts: 3
LukeExcelKid is on a distinguished road
Default
Does anyone have any advice? Thanks in advance.
Reply With Quote
  #3  
Old 11-17-2013, 02:39 AM
macropod's Avatar
macropod macropod is offline Macro to save to a single PDF Windows 7 32bit Macro to save to a single PDF Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,518
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 11-17-2013, 06:57 AM
LukeExcelKid LukeExcelKid is offline Macro to save to a single PDF Mac OS X Macro to save to a single PDF Office for Mac 2011
Novice
Macro to save to a single PDF 		 
Join Date: Nov 2013
Posts: 3
LukeExcelKid is on a distinguished road
Default
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?
Reply With Quote
  #5  
Old 11-17-2013, 01:39 PM
macropod's Avatar
macropod macropod is offline Macro to save to a single PDF Windows 7 32bit Macro to save to a single PDF Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,518
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
Try:
Code:
Private Sub CommandButton1_Click()
Dim wkSht As Worksheet, Ctrl As Control, i As Long, ArrShts()
ReDim Preserve ArrShts(0)
For Each Ctrl In Me.Controls
  With Ctrl
    If .Name Like "CheckBox#*" Then
      If .Value = True Then
        ReDim Preserve ArrShts(i)
        ArrShts(i) = .Caption
        i = i + 1
      End If
    End If
  End With
Next
If ArrShts(0) = 0 Then GoTo NoPrint
With ActiveWorkbook
  Set wkSht = .ActiveSheet
  .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()
Dim Ctrl As Control, i As Long, ArrShts()
ArrShts() = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", _
  "Jan (2)", "Feb (2)", "Mar (2)", "Apr (2)", "May (2)", "Jun (2)", _
  "Jul (2)", "Aug (2)", "Sep (2)", "Oct (2)", "Nov (2)", "Dec (2)")
  For Each Ctrl In Me.Controls
    With Ctrl
      If .Name Like "CheckBox#*" Then
        .Caption = ArrShts(i)
        i = i + 1
      End If
    End With
  Next
End Sub
Note: I couldn't help fiddling with your UserForm_Initialize sub a bit more ... The only change of substance is:
If ArrShts(0) = 0 Then GoTo NoPrint
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to save to a single PDF 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 to a single PDF 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:45 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft