jeudi 10 août 2017

Checkboxes not selecting sheets for PDF generation

I have a an Active X Button that when clicked brings up a Userform with checkboxes for each sheet in my workbook. What I am trying to do is allow the user to select which sheets they want to generate into a PDF. Currently, regardless of what checkboxes are selected once the user hits the enter button that generates the PDF, all the sheets in the workbook are included in the PDF not what was selected and the sub will not end automatically, I have to automatically go into the VBA and hit the stop button. So please if you could let me know what I am doing wrong with my code and why the checkboxes are not selecting the sheets I want to be generated into a PDF. Thanks in advance for all help!

Private Sub chbxEnter_Click()

Dim PDFsheets As String
Dim s As Worksheet
PDFsheets = "Approval Form,Business Plan,Deal Worksheet,All Manager Deal Recap,Deal Recap,MEC Dealership Profile,Loyal,Mid Loyal,Non Loyal,Projected Incentive Report,MEC"
ary = Split(PDFsheets, ",")

    If CheckBox1.Value = True Then
    PDFsheets = "Approval Form"
    End If

    If CheckBox2.Value = True Then
        If PDFsheets = "" Then
        PDFsheets = "Business Plan"
    Else
        PDFsheets = PDFsheets & ",Business Plan"
    End If
    End If

    If CheckBox3.Value = True Then
        If PDFsheets = "" Then
        PDFsheets = "Deal Worksheet"
    Else
        PDFsheets = PDFsheets & ",Deal Worksheet"
    End If
    End If


    If CheckBox4.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "Deal Recap"
    Else
        PDFsheets = PDFsheets & ",Deal Recap"
    End If
    End If

    If CheckBox5.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "All Manager Deal Recap"
    Else
        PDFsheets = PDFsheets & ",All Manager Deal Recap"
    End If
    End If

    If CheckBox6.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "MEC Dealership Profile"
    Else
        PDFsheets = PDFsheets & ",MEC Dealership Profile"
    End If
    End If

    If CheckBox7.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "Loyal"
    Else
        PDFsheets = PDFsheets & ",Loyal"
    End If
    End If

    If CheckBox8.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "Mid Loyal"
    Else
        PDFsheets = PDFsheets & ",Mid Loyal"
    End If
    End If

    If CheckBox9.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "Non Loyal"
    Else
        PDFsheets = PDFsheets & ",Non Loyal"
    End If
    End If

    If CheckBox10.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "Projected Incentive Report"
    Else
        PDFsheets = PDFsheets & ",Projected Incentive Report"
    End If
    End If

    If CheckBox11.Value = True Then
    If PDFsheets = "" Then
       PDFsheets = "MEC"
    Else
        PDFsheets = PDFsheets & ",MEC"
    End If
    End If

ThisWorkbook.Sheets(ary).Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    strPath & strFName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

 ActiveWindow.SelectedSheets(1).Select


End Sub




Aucun commentaire:

Enregistrer un commentaire