如何禁用保存功能
问题描述:
我目前有一个宏,它可以进行数据挖掘并最终保存工作簿。我打算禁用工作簿的保存功能,并强制用户在每次需要保存工作簿时使用宏。这是我迄今为止的,但它似乎没有工作。当我这样做时,我的宏和下面描述的这个子都是循环运行的。每次我的宏试图保存工作簿时,该子文件都不允许它。我基本上想强制用户使用宏来保存工作簿。如何禁用保存功能
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim NoSave
NoSave = MsgBox("Changes have to be submitted before the workbook can be saved, Proceed and submit ?", vbYesNo, "Continue?")
If NoSave = vbNo Then
Cancel = True
Else
Main
End If
End Sub
答
这里是一个例子。粘贴在ThisWorkbook
。这不会让你使用Save
或SaveAs
。但是,您可以使用宏SaveThisFile
来保存工作簿。请修改它以适应您的需求。
Option Explicit
Dim SaveByCode As Boolean
Const msg As String = "Please use the macro to save the file"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False And SaveByCode = False Then
MsgBox msg, vbExclamation, "Unable to save"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
If SaveByCode = True Then
SaveThisFile
Else
MsgBox msg, vbExclamation, "Unable to save"
Cancel = True
End If
Application.EnableEvents = True
End Sub
'~~> Your macro to save the file
Sub SaveThisFile()
SaveByCode = True
ThisWorkbook.Save
End Sub
注意:如果您保存宏是在一个模块中,然后从ThisWorkbook
删除此Dim SaveByCode As Boolean
并将Public SaveByCode As Boolean
一个模块中。
+0
非常感谢! – user1452091 2012-07-24 13:22:42
答
替代,这个怎么样(我误解了这个问题,在第一,但也想给它一个尝试,因为它是有趣的):
声明公共布尔(例外)放在ThisWorkbook模块中:
Option Explicit
Public bSave As Boolean
在事件BeforeSave事件:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sNoSave As String
If bSave = True Then
bSave = False
Exit Sub
End If
sNoSave = MsgBox("Changes have to be submitted before the workbook can be saved, Proceed and submit ?", vbYesNo, "Continue?")
If sNoSave = vbNo Then
bSave = False
Cancel = True
Exit Sub
Else
bSave = True
Call Main(bSave)
End If
End Sub
在主营:
Explicit选项
Sub Main(bSave)
If bSave = True Then
ThisWorkbook.SaveAs Filename:="U:\Book1.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
MsgBox "Main method called"
End If
End Sub
难道你不能只保存你的宏,并在BeforeSave事件上运行你的宏? – JMK 2012-07-24 12:30:43
工作正常!好主意,谢谢 – user1452091 2012-07-24 13:19:03