Multiple pdf from excel using check box

Question

My challenge is to export multiple excel sheets by using checkbos. I have done the script as below, but it only works if one check box is selected. The row "Sheets(Array(tuloste)).Select" is giving error when both or none checkboxes are selected.

Private Sub CommandButton1_Click()
Dim nimi
Dim tarjousnumero
Dim polku
Dim sivut As String
Dim Liite1
Dim Liite2
If CheckBox1.Value = True Then
Liite1 = "Palvelukuvaus"
Else
Liite1 = ""
End If
If CheckBox2.Value = True Then
Liite2 = "Etävalvonta"
Else
Liite2 = ""
End If
tuloste = Liite1 & Liite2
ActiveWorkbook.Save
Range("I2").Activate
tarjousnumero = ActiveCell
nimi = InputBox("Anna tiedoston nimi", "Tallenna pdf muodossa", "Tarjous " & tarjousnumero)
polku = ActiveWorkbook.Path
Sheets("Kaupallinen tarjous").Select
Sheets(Array(tuloste)).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=polku & "\" & nimi & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _OpenAfterPublish:=False
Sheets("Kaupallinen tarjous").Select
UserForm2.Hide
End Sub

Obligatory [How to avoid select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Warcupine, Oct 06 '20 at 17:46
Your code is really hard to read without indentation and with this foreign names. I assume your problem is that no checkbox is selected, `tuloste` holds an empty string. What do you expect should happen when you issue `Sheets(Array("")).Select`? Or, with other words, what do you want to export in that case? — FunThomas, Oct 06 '20 at 17:51

Marko Nurmi · Answer 1 · 2020-10-07T04:25:44.627

Sorry -- below with less foreign word. I want to make a pdf_printout with have one fixed sheet (Kaupallinen tarjous) and attacments that I can select by chackboxes. The final pdf_printout can have 0 - 2 attachment at this moment. It may become more check boxes to select , so more dynamic code would be also appreciated. However for now I would be OK to have 0 - 2 attachments. I solved the problem with having "" value for printout by using if-condition, but still struggling to select two checkboxes.

Private Sub CommandButton1_Click()

Dim file
Dim offernumber
Dim path
Dim pages As String
Dim printout As String
Dim Appendix1 As String
Dim Appendix2 As String


If CheckBox1.Value = True Then
Appendix1 = "Our services"
Else
Appendix1 = ""
End If

If CheckBox2.Value = True Then
Appendix2 = "Remote control"
Else
Appendix2 = ""
End If

printout = Appendix1 & Appendix2

ActiveWorkbook.Save
Range("I2").Activate
offernumber = ActiveCell
file = InputBox("Anna tiedoston file", "Tallenna pdf muodossa", "Tarjous " & 
offernumber)
path = ActiveWorkbook.Path
Sheets("Kaupallinen tarjous").Select

if printout="" then
Sheets(“Kaupallinen tarjous”).Select
else
Sheets(array(“Kaupallinen tarjous”, printout)).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "\" & file & ".pdf", 
Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
Sheets("Kaupallinen tarjous").Select

UserForm2.Hide

End Sub

Multiple pdf from excel using check box

1 Answers1