mercredi 23 janvier 2019

Loop through checkboxes in VBA

I'm trying to create a code to loop through a userform to see if the checkboxes are checked and then create a PDF of the sheets. However I get the error "object required". I hope you can help me :)

Option Explicit


Sub Get_PDF_Click()

Dim ReportName As String
ReportName = ActiveSheet.Cells(4, 20).Value
Dim OutputPath As String
OutputPath = "T:\5. Fælles\Optima Lancering Version 1.0\6. Oversigtsark\KontoInvest\Historik\"
Dim YYMM As Variant
Dim Name_of_File As Variant
Dim Name As Variant
Dim x1TypePDF As Variant
Dim cbox As CheckBox
Dim FormControlType As Variant
Dim shp As Shape
Dim PDFUserForm As UserForm


YYMM = Format(WorksheetFunction.EoMonth(Now(), -1), "YYMM")

Name = "BankNordik_faktaark" & YYMM
Name_of_File = OutputPath & "BankNordik\" & Name

 '------- LOOP THROUGH CHECKBOXES -------
For i = 0 To 6 'LBound(Kunde_Array) To UBound(Kunde_Array)
    For Each shp In PDFUserForm
    If shp.FormControlType = xlCheckBox _
    And shp.ControlFormat.Value = True Then
            For Each Ws In Worksheets
                If Ws.Name = "Fakta " & Kunde_Array(i + 1, 1) Then
                    ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, Filename:= _
                    Name_of_File, Quality _
                    :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                    :=False, OpenAfterPublish:=False
                End If
            Next Ws
        End If
    'End If
    Next shp
Next i

End sub




Aucun commentaire:

Enregistrer un commentaire