用户表单中的复选框可选择要打印的工作表 [英] Check boxes in user form to select sheets to print

问题描述

我有代码，选择要打印到pdf文档的表格数组，但我试图实现一个用户表单，其中有一系列与特定表格相对应的复选框。

I have code that selects an array of sheets to be printed to a pdf document, however I am trying to implement a user form with a series of check boxes that correspond to a specific sheets.

下面的代码选择一个预定的数组，并将它们打印为pdf。

The following code selects a predetermined array of sheets and prints them as a pdf

Sub PDFAllSheets_Click()

Dim ws As Worksheet
Dim strPath As String
Dim myfile As Variant
Dim strFile As String
Dim sheetstoprint As String

On Error GoTo errHandler

Set ws = ActiveSheet

strFile = "E_CALC_" & Worksheets("Contents").Range("H7").Text & ".pdf"

strFile = ThisWorkbook.Path & "\" & strFile

myfile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    title:="Select Folder and FileName to save")

If myfile <> "False" Then

    ThisWorkbook.Sheets(Array("Engine", "CHP Layout", "Ventilation", "Exhaust", "Gas", "Hazardous Zoning", "Gas Ramp up", "Steam Boilers", _
                        "JW PU", "AC PU", "Combustion", "BREEAM NOx", "Pump P1", "Pump P2", "Pump P3", "Pump P4", "Pump P5")).Select

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=myfile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

    MsgBox "PDF file has been created."

End If

exitHandler:
Exit Sub

errHandler:
MsgBox "Could not create PDF file", vbRetryCancel, "Oops!"

Resume exitHandler

End Sub

I需要以下UserForm的复选框来定义要包含在数组中的工作表。

I need the following UserForm's check boxes to define the sheets to include in the array.

UserForm1

推荐答案

如果你有一个带有ListBox和CommandButton的UserForm，这应该工作，假设你有你指定的工作表命名。

If you have a UserForm with a ListBox and a CommandButton, this should work, assuming you have sheets named as you specified.

这个代码当然应该添加到UserForm代码模块。

This code should of course be added to the UserForm code module.

Private Sub CommandButton1_Click()

Dim SheetArray() As Variant
Dim indx As Integer

Dim ws As Worksheet
Dim strPath As String
Dim myfile As Variant
Dim strFile As String
Dim sheetstoprint As String

On Error GoTo errHandler

Set ws = ActiveSheet

strFile = "E_CALC_" & Worksheets("Contents").Range("H7").Text & ".pdf"

strFile = ThisWorkbook.Path & "\" & strFile

myfile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Select Folder and FileName to save")

If myfile <> "False" Then

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    indx = 0
    For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) = True Then
                ReDim Preserve SheetArray(indx)
                SheetArray(indx) = Sheets(ListBox1.List(i, 1)).Index
                indx = indx + 1
            End If
    Next i

    If indx > 0 Then

            Sheets(SheetArray()).Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=myfile, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True


                '.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


    End If
End If
exitHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub

errHandler:
MsgBox "Could not create PDF file", vbRetryCancel, "Oops!"

Resume exitHandler
End Sub

Private Sub UserForm_Initialize()

Dim wks() As Variant


wks = Array("Engine", "CHP Layout", "Ventilation", "Exhaust", "Gas", "Hazardous Zoning", "Gas Ramp up", "Steam Boilers", _
                        "JW PU", "AC PU", "Combustion", "BREEAM NOx", "Pump P1", "Pump P2", "Pump P3", "Pump P4", "Pump P5")

'Debug.Print wks(16)
For i = 0 To UBound(wks)

        ListBox1.AddItem wks(i)
        ListBox1.List(ListBox1.ListCount - 1, 1) = wks(i)

Next i


End Sub

记住在列表框属性窗口中允许列表框多选。

Remember to allow listbox multiselect in the listbox properties window.

编辑：
在测试期间似乎Excel应用程序导出PDF后冻结。我不知道它是否与OpenAfterPublish属性设置为True有关，因为我一直设置为False。

During my testing, it seems that the Excel application freezes after exporting the PDF. I don't know if it has anything to do with the OpenAfterPublish property being set to True as I've always set it to False.

Edit2：

我的错误，这只是因为UserForm仍然打开...

My mistake, It's simply because the UserForm is still open ...

这篇关于用户表单中的复选框可选择要打印的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！