时间戳

首页 > 解决方案 > 取消保存对话框时退出每个循环

excel - 取消保存对话框时退出每个循环

问题描述

我是 VBA 的新手，作为我自己开发的一部分，我目前正在重写一些我在大约 6 个多月前完成的自动化。我有下面的代码，它根据按下按钮的工作表来识别工作表名称（有两个不同的工作表名称类似于CustName_CALC和两个按钮）。然后它会遍历工作簿中的剩余工作表以查找“相似”工作表。

下面的 subPDF_Bill()应用于前面提到的按钮：

Public vPDFilename As Variant
Public wb As Workbook
Public ws As Worksheet
Public wsBill As Worksheet
Public wsCalc As Worksheet
Public wsStatement As Worksheet
Public sLocation As String, sCustName As String, myTitle As String, myMsg As String, InitialFileName As String, Response As String, C_Response As String

Sub PDF_Bill()

sCustName = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") - 1)

Set wb = ThisWorkbook
Set wsCalc = wb.Sheets(sCustName & "_CALC")

myTitle = "Save Invoice"
myMsg = "Are you sure you would like to save the " & wsCalc.Cells(1, 2).Value2 & " invoice?"
Response = MsgBox(myMsg, vbQuestion + vbOKCancel, myTitle)
Select Case Response
    Case Is = vbOK

For Each wsBill In ThisWorkbook.Worksheets
        If wsBill.Name Like sCustName & "_BILL" & "*" Then
                    Call Module1_PDF.PDF_Procedure
        End If       
    Next wsBill

    Case Is = vbCancel
        'user cancels the first popup message
        myTitle = "Invoice Cancelled!"
        myMsg = "You've cancelled the request to save the invoice!"
        C_Response = MsgBox(myMsg, vbOKOnly, myTitle)

End Select
End Sub

上面这样称呼：

Sub PDF_Procedure()

sCustName = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") - 1)
sLocation = "S:DRIVELOCATION" & wsCalc.Cells(1, 2).Value2 & "\Invoices\"

vPDFilename = Application.GetSaveAsFilename( _
InitialFileName:=sLocation _
                    & wsCalc.Cells(1, 2).Value2 _
                    & " " _
                    & MonthName(Month(Date)) _
                    & " Invoice", _
FileFilter:="PDF, *.pdf", _
Title:="Save as PDF")

If vPDFilename <> False Then
    With wsBill
        '.Visible = xlSheetVisible '[will be hidden at later state]
        '.Activate
        'predefined area for 5 page invoice - this may need to change in future.
        .PageSetup.PrintArea = "A1:S300"
    End With

    'creates the PDF
    wsBill.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=vPDFilename, _
    OpenAfterPublish:=False

    'wsBill.Visible = xlSheetHidden 'bill sheet HIDE [WILL BE USED LATER NOT SET UP]
    Else
      'if user cancels save dialog box
      myTitle = "Invoice Cancelled!"
      myMsg = "You've cancelled the request to save the invoice!"
      C_Response = MsgBox(myMsg, vbOKOnly, myTitle)
      wsCalc.Activate
      GoTo CancelProcess    
End If
CancelProcess:
Exit Sub
End Sub

上面的代码（除了GoTo CancelProcess因为那是我正在努力解决的问题）对于一张调用的工作表非常有效，CustName_Bill但我也需要它在下面的场景中工作。

我已经查看了以下页面以及更多内容，但均未成功：

我知道我需要在exit某个地方发表声明，但我很难理解它的去向。我见过的很多例子都不够复杂，不足以让我得出如何解决我的问题的结论。

我目前的问题是我有两张名称相似的工作表（故意）：

循环继续到下一张纸，即CustName_Bill_Type1当用户取消Application.GetSaveAsFilename但我需要在用户取消第一个对话框for each时退出循环。 Application.GetSaveAsFilename

exit所以我的问题是，一旦用户在第一个保存对话框上单击取消，我需要将代码放在哪里才能完全终止。

标签： excelvba

解决方案

您需要将 sub 转换为函数，以便在用户取消保存时返回 FALSE。

Function PDF_Procedure() as Boolean

    sCustName = Left(ActiveSheet.Name, InStr(ActiveSheet.Name, "_") - 1)
    sLocation = "S:DRIVELOCATION" & wsCalc.Cells(1, 2).Value2 & "\Invoices\"

    vPDFilename = Application.GetSaveAsFilename( _
    InitialFileName:=sLocation _
                        & wsCalc.Cells(1, 2).Value2 _
                        & " " _
                        & MonthName(Month(Date)) _
                        & " Invoice", _
    FileFilter:="PDF, *.pdf", _
    Title:="Save as PDF")

    If vPDFilename <> False Then
        With wsBill
            '.Visible = xlSheetVisible '[will be hidden at later state]
            '.Activate
            'predefined area for 5 page invoice - this may need to change in future.
            .PageSetup.PrintArea = "A1:S300"
        End With

        'creates the PDF
        wsBill.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=vPDFilename, _
        OpenAfterPublish:=False

        ' return true if saved
        PDF_Procedure = True   
        'wsBill.Visible = xlSheetHidden 'bill sheet HIDE [WILL BE USED LATER NOT SET UP]
    Else
        'if user cancels save dialog box
        myTitle = "Invoice Cancelled!"
        myMsg = "You've cancelled the request to save the invoice!"
        C_Response = MsgBox(myMsg, vbOKOnly, myTitle)
        wsCalc.Activate

        'return false if cancelled
        PDF_Procedure = False   
    End If

End Function

并为您的 PDF_Bill 子添加条件

For Each wsBill In ThisWorkbook.Worksheets
    If wsBill.Name Like sCustName & "_BILL" & "*" Then
        If Module1_PDF.PDF_Procedure() = False Then
            Exit sub
        End if
    End If       
Next wsBill

推荐阅读