vba - Excel 2013 切换只读

Question

我有一个用 Excel 2003 编写的应用程序，最近已升级到 Excel 2013。在 workbook_open 事件中，我将工作簿设置为只读 ActiveWorkbook.ChangeFileAccess xlReadOnly 并使用切换只读按钮可在读\写和只读之间切换。在 Excel 2003 中，当切换文件模式时，工作簿将按预期切换。当我在 2013 年将其作为 .xlsm 运行时，切换文件状态后会调用 Workbook_Open 事件，并且它再次变为只读。

Private Sub Workbook_Open()
    If ActiveWorkbook.ReadOnly = False Then
        ActiveWorkbook.Saved = True
        ActiveWorkbook.ChangeFileAccess xlReadOnly
    End If
End Sub

Answer 1

无需将 FileAccess 更改为只读即可实现您想要的结果。您可以使用 Workbook_BeforeSave 和 Workbook_Beforeclose 事件来控制保存工作簿的能力。我在下面提供了一个完整的代码示例，我相信它会满足您的需求。您可以使用切换按钮或您选择的任何方法来运行 subMakeItSaveable 和 subMakeItUnSaveable，或者您可以在单个例程中实现该功能。funUpdateCustomDocumentProperty 函数将 bool 值写入工作簿自定义属性以切换保存工作表的功能。请注意，此自定义属性除了提供一个位置来存储不在代码或工作表中的值之外，不会执行任何操作。这提供了一种在代码未运行时保存代码所需数据的便捷方法。

我使用的代码如下:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      ThisWorkbook.Saved = True
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      If ThisWorkbook.CustomDocumentProperties("SaveMyChanges").Value Then
        SaveAsUI = True
        Cancel = False
      Else
        SaveAsUI = False
        Cancel = True
        myTestValue = MsgBox("Read Only Workbook. Save Not Allowed.",         vbInformation, "Operation Aborted")
      End If
    End Sub

    Private Sub Workbook_Open()
      myTestValue = funUpdateCustomDocumentProperty("SaveMyChanges", False, msoPropertyTypeBoolean)
    End Sub

    Public Function funUpdateCustomDocumentProperty(strPropertyName As String, _
     varValue As Variant, docType As Office.MsoDocProperties) As Boolean
    'Function returns true if custom property was added, false if it already exists
    'Originally a sub built by Peter Albert
    'http://stackoverflow.com/users/1867581/peter-albert

      On Error Resume Next
      funUpdateCustomDocumentProperty = False
      ThisWorkbook.CustomDocumentProperties(strPropertyName).Value _
    = varValue

      If Err.Number > 0 Then

        ThisWorkbook.CustomDocumentProperties.Add _
          Name:=strPropertyName, _
          LinkToContent:=False, _
          Type:=docType, _
          Value:=varValue
        funUpdateCustomDocumentProperty = True
      End If
    End Function


    Public Sub subMakeItSaveable()
      myTestValue = funUpdateCustomDocumentProperty("SaveMyChanges", True, msoPropertyTypeBoolean)
    End Sub


    Public Sub subMakeItUnSaveable()
      myTestValue = funUpdateCustomDocumentProperty("SaveMyChanges", False, msoPropertyTypeBoolean)
    End Sub