PDA

View Full Version : Prevent user edits to a workbook without making the workbook dirty



pdhudgens
04-14-2015, 02:26 PM
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
04-14-2015, 10:41 PM
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
04-15-2015, 12:23 AM
Could you not just open it read only so they can't save any changes they do make?

Yongle
04-15-2015, 12:57 AM
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
04-15-2015, 01:03 AM
@Aflatoon - I think your solution wins my vote - simple, sensible, perfect!

Aflatoon
04-15-2015, 01:03 AM
Just be aware that using a lot of linked pictures will reduce your VBA to a crawl.

Yongle
04-15-2015, 02:27 AM
@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" :jester::giggles:

pdhudgens
04-15-2015, 09:35 AM
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