Suppress Save request on Read-only file

[dlangschied]

I have a workbook that provides drop down list data to group of worksheets. It needs to be open, but I did not want it visible to the user. So, I open it as:

Sub Auto_Open()
Application.ScreenUpdating = False
Set w = Workbooks
w.Open Filename:="P:\Troy Personnel\2016\Summary\Personnel Budget Data.xlsm", UpdateLinks:=False, ReadOnly:=True 'this is the data file were going to be opening
ActiveWindow.Visible = False
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub

This works perfectly. However, when the user closes Excel, it asks them to Save the file. I do not want them to do that, which is why I opened in read-only mode. They still can't save the file, but it is a prompt that I wanted to suppress. I tried this:

Sub CloseandSave()
w.Close SaveChanges:=False
End Sub

and this:

Sub Workbook_BeforeClose(Cancel as Boolean)
w.Close SaveChanges:=False
End Sub

Neither work, and I tried to place messages into the close and it looks like it is not even running. Please help this is a major pain!

Regards,

David

[AlphaFrog]

I have a workbook that provides drop down list data to group of worksheets.

It may be easier to put the lists on a hidden sheet in the same workbook. The lists would be hidden from the users and you wouldn't have to mess with opening\closing a 2nd workbook.

[judgeh59]

try putting this at the beginning of the SUB

Please Login or Register  to view this content.

and this at the end of the SUB

Please Login or Register  to view this content.

[cyiangou]

This will pretend that there were no changes made:

Please Login or Register  to view this content.

[dlangschied]

There are 20 workbooks that use this sheet. Any changes require changes to all 20. This is sensitive data as well. The owners do not want me to see the data.

[dlangschied]

All the things that I tried did not work. As it turned out it was my perception of WHERE the save was coming from. I was trying to add it to my macro, but no dice. I added the save to the workbook with the lists using beforeclose() and it worked.

I am sorry to bother all of you.

I am finding the Excel macro path to be very confusing. I do not, however, have any choice. It is not that Excel is a bad program, but it is very complex.

[cyiangou]

A good way to learn vba is to macro record things you normally do manually, and then study the generated code.