用户表单中的复选框可选择要打印的工作表 [英] 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.
推荐答案
如果你有一个带有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屋!