确定是否在VBA中使用保存、不保存或取消
确定是否在VBA中使用保存、不保存或取消
提问于 2020-12-25 12:41:04
在保存文档之前，我使用Workbook_BeforeSave事件来检查是否所有必填单元格都已填写。如果有任何必填单元格为空，它将要求用户在保存文档之前填充这些单元格。
代码运行情况与预期一致。但我面临的唯一问题是，当用户在没有填写强制单元格的情况下单击Close(X)时，excel会提示用户是否要在关闭工作簿之前保存/不保存更改，或者取消提示，如果用户单击Save，则会调用Workbook_BeforeSave事件，并通知用户有空的强制单元格需要填充，然后突然关闭文档。
我不想在用户单击“保存”时关闭工作簿，因为有强制单元格需要填充。如果他单击了“不保存确定”，则在不填写强制单元格的情况下关闭文档。
如何做到这一点。
代码，
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb As Workbook
Dim ws1 As Worksheet
Dim userange, userange1, userange2 As Range
Dim iCell, positionrng As Range
Dim usedrow As Long
Dim usecolumn As Long
Dim rowposition As Long
Dim ws1lastrow, lastcol As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("CAPEX FY21-22")
rowposition = 9
criteria = ws1.Range("BM9").Value
investment = ws1.Range("BT9").Value
roi = ws1.Range("BU9").Value
justification = ws1.Range("BW9").Value
ws1lastrow = ws1.Cells(Rows.Count, criteria).End(xlUp).Row
If ws1.Cells(rowposition, criteria).Value = "" Or ws1.Cells(rowposition, criteria).Value = Empty Then
GoTo exiting:
End If
Set userange1 = ws1.Range(ws1.Cells(rowposition, criteria), ws1.Cells(ws1lastrow, investment))
Set userange2 = ws1.Range(ws1.Cells(rowposition, roi), ws1.Cells(ws1lastrow, justification))
Set userange = Union(userange1, userange2)
For Each iCell In userange
If IsEmpty(iCell) = True Then
MsgBox ("Document cannot be saved!" & vbCrLf & "Mandatory cell(s) are empty!" & vbCrLf & "Please fill the highlighted cell to save.")
iCell.Activate
ActiveCell.Interior.Color = RGB(255, 255, 0)
Application.Goto ActiveCell, Scroll:=True
Cancel = True
Exit Sub
GoTo exiting:
End If
Next
saves:
MsgBox ("Document saved")
exiting:
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End SubStack Overflow用户
发布于 2020-12-25 16:34:41
我建议你重新组织你的代码。你的Workbook_BeforeSave做了太多的事情。想一想如何在不同的subs和功能之间划分职责。我之所以这么说，是因为Workbook_BeforeClose事件处理程序需要使用Workbook_BeforeSave中的大部分逻辑。
我建议采用以下结构：
编写一个函数，该函数将返回是否可以保存工作簿。这个函数将包含几乎所有的当前代码。您可以将其保存在标准模块中
Function IsOKToSave() As Boolean
' Returns True if all mandatory cells are filled
' Otherwise, returns False
End Function在事件处理程序中使用此函数。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iResponse As Integer
' Handle the saving yourself
' first check if the workbook has changed since it was last saved
If Not Me.Saved Then
If IsOkToSave Then
iResponse = MsgBox("Do you want to save changes to '" & Me.Name & "'?", vbYesNoCancel)
If iResponse = vbYes Then
Application.EnableEvents = False
Me.Save
Application.EnableEvents = True
ElseIf iResponse = vbCancel Then
Cancel = True
Else
' close without saving
Me.Saved = True
End If
Else
iResponse = MsgBox("Document cannot be saved!" & vbCrLf & _
"Mandatory cell(s) are empty!" & vbCrLf & vbCrLf & _
"Do you want to close WITHOUT saving?", vbYesNo + vbDefaultButton2)
If iResponse = vbYes Then
' close without saving
Me.Saved = True
Else
' Cancel closing the workbook
Cancel = True
End If
End If
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not IsOkToSave Then
Cancel = True
MsgBox "Document cannot be saved!" & vbCrLf & _
"Mandatory cell(s) are empty!" & vbCrLf & _
"Please fill the highlighted cell to save."
End If
End Sub还要注意，您需要声明每个变量的类型。因此，这条线
Dim userange, userange1, userange2 As Range应该是
Dim userange As Range, userange1 As Range, userange2 As Range否则，userange和userange1的类型将为Variant
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65445332
复制相关文章
相似问题
腾讯云开发者
