Save on close

ExcelSJ

New Member
Joined
May 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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 :)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
Not tested here. See if this works for you :

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

ExcelSJ

New Member
Joined
May 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Unfortunately not. It doesn't save the newly added data.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
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

New Member
Joined
May 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
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

New Member
Joined
May 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
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

New Member
Joined
May 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,301
If the workbook is no longer "Read Only" there should be no reason why the links would not update.
 
Solution

Forum statistics

Threads
1,148,370
Messages
5,746,299
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top