Shared Workbook Security.

[Philip1957]

Greetings,

Windows 7
Excel 2013

I have a workbook that resides on our network where users can look up various types of information and I need to protect it from unauthorized changes. This is pretty simple with Worksheet and Workbook protection set from the Review tab but I also need to keep people from saving a copy to their own desktop (this is to ensure they are always going to the network for the most current revision).

I have the following VBA code in "This Workbook" to disable the save function:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Prevents users from saving changes.

    MsgBox "You can't save this workbook!"
    Cancel = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Blocks the save reminder
        Application.ThisWorkbook.Saved = True
End Sub

This works fine but many of the users are familiar with VBA. I have my VBA Project password protected so they can't comment block the code but they can always just click the Design Mode button to keep the code from running and save their changes anyway.

How can I disable the Design Mode buttons? I've tried putting various versions of

CommandBars("Visual Basic").Controls("Design Mode").enabled=False
CommandBars("Control Toolbox").Controls("Design Mode").enabled=False

that I've seen here and elsewhere into my Workbook Open sub but none of them have worked.

Now here's where it gets tricky.

In order for me to save the changes I am making to my workbook (and I need to update if once or twice a week) I have to either comment out the code that prevents saving or go to design mode. If I add code to disable the Design Mode buttons I won't be able to to save the workbook after I uncomment block the code.

Is there any way to create a sub that will ask the user for a password when they click one of the Design Mode buttons and then deny them access if they don't have it?

As always, any assistance is greatly appreciated.

~ Phil