Don't Save Read-Only Excel File

[andyclear]

Hi,

I have a read-only file with a userform which allows multiple people to use the userform without have the read-only prompt come up. I have done this so that I don't have to use sharing feature in the workbook as their are multiple issues that occur when doing so.

I would like people to be able to use the userform which populates a different workbook with the data they input into this userform and not have a save prompt occur when they close the read-only workbook.

thanks.

 

[Logit]

Here are two small macros for you to review, with comments:

Option Explicit


[COLOR=#ff0000]' Macro [B]One[/B]: When workbook closes, there will be no request for SAVING, 
' and any changes made to the workbook WILL NOT be saved.
[/COLOR]

Private Sub Workbook_BeforeClose(Cancel As Boolean)


    Application.DisplayAlerts = False  [COLOR=#ff0000]'suppresses all messages about saving[/COLOR]
    ActiveWorkbook.Close savechanges:=False  [COLOR=#ff0000]'FALSE = [B]changes are not saved[/B][/COLOR]
    Application.DisplayAlerts = True [COLOR=#ff0000]'allows message to show after workbook closes[/COLOR]

End Sub

Option Explicit


[COLOR=#ff0000]' Macro [B]Two[/B]: When workbook closes, there will be no request for SAVING,
' and any changes made to the workbook WILL BE SAVED.
[/COLOR]

Private Sub Workbook_BeforeClose(Cancel As Boolean)


    Application.DisplayAlerts = False  [COLOR=#FF0000]'suppresses all messages about saving[/COLOR]
    ActiveWorkbook.Close savechanges:=True [COLOR=#ff0000] 'TRUE = [B]changes ARE saved[/B][/COLOR]
    Application.DisplayAlerts = True  [COLOR=#FF0000]'allows message to show after workbook closes[/COLOR][COLOR=#FF0000]
[/COLOR]


End Sub

Use one of the above macros, depending if you want to save any changes ... or if you don't want to save any changes. Either selection, when used, will be done automatically without
user input. The action occurs and the workbook closes.

Place the macro of your choice in the ThisWorkbook module in the VBE editor.

 

[andyclear]

Thank You Very much both work perfectly.

 

[Logit]

You are welcome.

 

[Logit]

Andy:

Received your private message. However, the FORUM I suspect is acting up ? When trying to respond I'm receiving an alert that your mailbox is full and cannot accept any more messages.

I'll keep trying.

 

[andyclear]

Hi Logit,

Not sure why but can't receive more than one message.

How ever if you could reply to my message within this post regarding the slight issue with the new blank excel workbook that opens up after I close my workbook that be awesome.

 

[bebegaga]

well, recently, I read a post about read-only file, I am not sure is it relevant to your topic, but it is talking what to do when face read-only file on excel~
[TABLE="width: 113"]
<colgroup><col width="113" style="width:84.75pt;"> </colgroup><tbody>[TR]
[TD="width: 113"]https://www.fonecope.com/disable-read-only-in-excel-2016.html[/TD]
[/TR]
</tbody>[/TABLE]