Macro to open a workbook as read only

  • Thread starter Thread starter Sliders919
  • Start date Start date
S

Sliders919

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.
 
Why don't you just protect the workbook with a password and Excel will prompt
the user for the password or force them to click Read Only. This will open
your workbook as read-only.

Open your workbook, click Save As, then click Tools>General Options. You
then can set passwords.

Hope this helps! If so, click 'YES' below.
 
Back
Top