Save on close

[ExcelSJ]

I have a macro-enabled workbook which is read-only (data is recorded on the workbook through the use of macros). I need it to automatically save when closed. The following code saves the workbook on closing but it doesn't save the data recorded by the macros.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = False
End Sub

I've tried adding in the remove read-only attribute and add read-only attribute at the start and end of the above code but this just alerts that somebody else is in the workbook.

Any help appreciated

 

[Logit]

Not tested here. See if this works for you :

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

 

[ExcelSJ]

Unfortunately not. It doesn't save the newly added data.

 

[Logit]

There must be something askew with your workbook. Sadly I am unable to say what without having a copy of it.
There isn't any reason I am aware of the workbook would not save the data.

?????

 

[ExcelSJ]

Thanks for the reply and suggestion. Will take a look at the workbook and see if I can solve the issue.

 

[Logit]

If you like ... remove any confidential data from the workbook. Then post it for download on a Cloud site (DropBox.com, etc).

Post the download link here.

 

[ExcelSJ]

Dropbox

www.dropbox.com

 

[Logit]

Question: Any changes made to the workbook when it has been opened in READ ONLY status ... are those changes made by individuals or are the changes
made automatically by links to other workbooks or URLs (these change are done automatically without human intervention)?

 

[ExcelSJ]

I've removed READ ONLY and saved the file within my own OneDrive folder. If I then 'share' the document with colleagues via the Error... link, the auto save on close appears to work fine now.

The workbook above contains links to another workbook within the same folder in my OneDrive so ideally I would like the links to automatically update when somebody opens the workbook and not be prompted to update the links. Is this possible?

 

[Logit]

If the workbook is no longer "Read Only" there should be no reason why the links would not update.