Prevent user edits to a workbook without making the workbook dirty

[pdhudgens]

I've spent some serious time now trying to figure out how to do the following: Open a workbook programmatically (using VBA in Excel 2013) and prevent the user from making any changes - without making the workbook dirty. I want the user to be able to move around in any worksheet - and do searches - just not make any changes. If I programmatically Protect the workbook, it becomes dirty and other users are prevented from looking at the same file - at least in all the various ways that I've tried it. Does anyone have any thoughts?

Thanks very much for any help,
Paul Hudgens
Denver

[Yongle]

Welcome to the forum pdhudgens

Disable the save buttons

Private Sub Workbook_Open() 
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False 
    Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False 
End Sub

and to prevent ctrl + s

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox ("This worksheet cannot be saved")
End Sub

To avoid frustration when you want to amend the file yourself, create a macro to cancel the disable so that you can save OR else keep a separate copy of the original file without the disable function, and only put the above macros in the file the user accesses.

[Aflatoon]

Could you not just open it read only so they can't save any changes they do make?

[Yongle]

Also consider the Camera tool
This may not be the best approach here, but it is another option that is wonderfully useful :

If the user does not need to see the content of cells (ie formula etc)
Using the Camera tool, take a "photo" of what the user will look at and place "photos" in new sheets and then hide all the other sheets.
The "photo" is updated if the underlying data is amended
This way the user gets nowhere near the data. You could even protect the sheets containing the "photo"
For me, the real beauty of the camera tool is how easy it is to provide different "views" for users with different needs

[Yongle]

@Aflatoon - I think your solution wins my vote - simple, sensible, perfect!

[Aflatoon]

Just be aware that using a lot of linked pictures will reduce your VBA to a crawl.

[Yongle]

@Aflatoon - agreed but think of the side-benefit of crawling vba tying up the user's PC thus reducing the amount of time available to making the file "dirty"

[pdhudgens]

Thanks very much for your responses. For whatever reason, the stuff that wasn't working yesterday - is working today. I've thoroughly tested the following code and it works. I can only assume that it has something to do with shutting down my computer after having made several changes yesterday. Protecting the worksheet is now not making the workbook dirty - and therefore it remains accessible to other users. My program creates dozens of modules of the following code for various sets of data - each connected to a button in the main application. If anyone sees anything that doesn't look kosher please let me know.

Public Sub RigData1()
    Dim ThisFile As String
    Dim ThisFileName As String
    Dim Wks As Worksheet
    ThisFile = "E:\RigReports\285634_anreport_daily_north_2015-04-13.xls"
    ThisFileName = "285634_anreport_daily_north_2015-04-13.xls"
    If Not WkBkIsOpen(ThisFileName) Then Workbooks.Open FileName:=ThisFile, ReadOnly:=True
    Workbooks(ThisFileName).Worksheets("PERMITS").Activate
    Set Wks = ActiveSheet
    Wks.Protect ("HudgieBear73")
    Columns("C:C").Select
    Selection.Find(What:="North Dakota", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Select
End Sub

Thanks,
Paul Hudgens
Denver