Excel 2013 toggle read only

Question

I have an application that I've written in Excel 2003 and I have recently upgraded to Excel 2013. In the workbook_open event I set the workbook to read-only ActiveWorkbook.ChangeFileAccess xlReadOnly and have used the Toggle Read Only button to switch between read\write and read only. In Excel 2003 when switching file mode the workbook would toggle as expected. When I run it in 2013 as an .xlsm the Workbook_Open Event is called after switching the file status and it becomes read only again.

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

When you toggle, you could write to a cell (or name, i.e. in name manager) what the state is supposed to be, then in the `Workbook_Open` event check to see what the state should be, then open accordingly. — guitarthrower, Feb 10 '15 at 16:54
@guitarthrower: The workbook is unable to save the changes when in read-only mode. When the mode is toggled from read-only to read-write I also loose all variables due to a complete reload of the workbook. — Ronson Lamond, Feb 10 '15 at 17:27
[slaps forehead] of course you can't save in read-only mode :) It sounds like when it toggles, it is reopening the file (I don't have 2013, so I can't test). If that is the case, you could try implementing a `Application.EnableEvents = False` at some point in your code. Another option could be to check the current status on open. It would help if you could post your `Workbook_Open` code. — guitarthrower, Feb 10 '15 at 20:38
@guitarthrower: I've created a new workbook with just this code (appended to the question) to test if there was something else causing the issues. I was able to replicate the error. I came across using window parameters to pass variables between workbooks whether or not they were open, this didn't even work. It appears that the when the file is toggled read only the window its self is terminated. Every time a new HWnd is assigned to the workbook. — Ronson Lamond, Feb 10 '15 at 20:50

Larry Swatsenberg · Answer 1 · 2015-02-14T03:09:07.473

It is possible achieve the results you are after without changing the FileAccess to read only. You can use the Workbook_BeforeSave and Workbook_Beforeclose events to control one's ability to save the workbook. I have provided a complete code example below that I believe would suit your needs. You could use a toggle button or any method you choose to run the subMakeItSaveable and subMakeItUnSaveable, or you could implement that functionality in a single routine. The funUpdateCustomDocumentProperty function writes a Boolean value to the workbooks custom properties to toggle the ability to save the sheet. Please note that this custom property doesn't do anything except provide a place to store a value that isn't in either code or in the sheet. This provides a handy method of persisting data that our code needs when the code isn't running.

The code I used follows:

    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

Excel 2013 toggle read only

1 Answers1