I'm using Excel 2007 and need a private macro that will make the workbook
read only when opening. I've found some code which is along the lines of
workbook.open filename etc etc readonly

First off I cant get it to work and secondly the workbook will potentially
be saved into numerous locations so dont want to keep editing macros.

My best solution is the code that changes the file access to readonly
running as a private macro on worksheet open (ActiveWorkbook.ChangeFileAccess
xlReadOnly). But now I get a pop up message saying "Do you want to save
changes before switching file status?". I want some code that will say "Yes"
without prompting the user. I've tried switching off the prompts (
Application.DisplayAlerts = False) but that doesn't work.

Help.