Listbox - Creating PDF

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I'm using the below coding through listbox to create PDF - it's working fine. However I want sheet ("Home") to be PDF by default at all times regardless of selection in the listbox or not - is it possible ?

Thanks

VBA Code:
Sub pdf_sh()
Dim i As Long, c As Long
Dim sheetarray() As String

With ActiveSheet.ListBoxSh
For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve sheetarray(c)
sheetarray(c) = .List(i)
c = c + 1
End If

Next i
End With

     pdfName = Sheets("Home").Range("B9").Value & "_" & Sheets("Home").Range("B17")
    ChDir "Test"
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
        Sheets(sheetarray()).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
        Worksheets("Home").Select
        Exit Sub

    End If
  
End Sub
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
Sub pdf_sh()
  Dim i As Long, c As Long
  Dim sheetarray() As String
  Dim pdfName As String, fileSaveName As Variant
  
  With ActiveSheet.ListBoxSh
    ReDim Preserve sheetarray(c)
    sheetarray(c) = "Home"
    c = c + 1
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve sheetarray(c)
        sheetarray(c) = .List(i)
        c = c + 1
      End If
    Next i
  End With
  
  With Sheets("Home")
    pdfName = .Range("B9").Value & "_" & .Range("B17")
    ChDir "Test"
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
      fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
      Sheets(sheetarray()).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
      .Select
    End If
  End With
End Sub
 

ashani

Board Regular
Joined
Mar 14, 2020
Messages
202
Office Version
  1. 365
Platform
  1. Windows
@DanteAmor - you're amazing thank you it works perfect.
Sorry last question - is it possible to have the same for another 2 worksheets (Sheet1 & Sheet2) export as PDF by default at all times. So in total 3 sheets by defaults - Home, Sheet1 & Sheet2.

Thank you once again.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
Sub pdf_sh()
  Dim i As Long, c As Long
  Dim sheetarray() As String
  Dim pdfName As String, fileSaveName As Variant
  Dim default As Variant
  
  default = Array("Home", "Sheet1", "Sheet2")
  
  With ActiveSheet.ListBoxSh
    For i = 0 To UBound(default)
      ReDim Preserve sheetarray(c)
      sheetarray(c) = default(i)
      c = c + 1
    Next
    
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve sheetarray(c)
        sheetarray(c) = .List(i)
        c = c + 1
      End If
    Next i
  End With
  
  With Sheets("Home")
    pdfName = .Range("B9").Value & "_" & .Range("B17")
    ChDir "Test"
    fileSaveName = Application.GetSaveAsFilename(pdfName, "PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
      Sheets(sheetarray()).Select
      ActiveSheet.ExportAsFixedFormat xlTypePDF, fileSaveName, xlQualityStandard, True, False, , , True
      .Select
    End If
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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