VBA to prevent other users from making any changes - a true Read Only

[Jim885]

I have an Excel file with many worksheets which already have certain cells locked and password protected to prevent users from making changes to the worksheets. So changing all cells in a worksheet to LOCKED upon opening the file will not work, since each sheet has various locked cells.

We want all employees to be able to open this file and be able to look at each sheet. However, we don't want them to be able to make entries into cells (which "read only" will still allow).

Is there any way to make a read-only file a true read-only?

 

[Lexcon07]

I can certainly appreciate your situation. I am often faced with "Users" that are frightened and/or easily intimidated by Excel Spreadsheets that seem too complicated or confusing. Many of my Excel Models contain over 200 worksheets and anyone who doesn't have at least a moderate degree of comfort in Excel would be lost. So, I developed a "User Interface" that allows me limit the user experience to a simple point and click process to get to whatever information they are looking for (kind of like navigating through a website). Users can look at high-level summaries (i.e. For Senior Management), Sector Specific Summaries (i.e. For Operational Directors), Operational Level Summaries (i.e. for Managers) or drill all the way down to the source data if you want to let them. Basically, you can design the data model any way you want. I hide all of the "Worksheet Tabs" from view because they no longer need them and because users get "intimidated" when they see a "million" tabs.

In my models, the "Administrators" are responsible for updating (i.e. source data) and maintaining (i.e. changes to input variables) the models. The models use the Password-Protected "Administrative Rights" feature to provide Administrators with the ability to lock and unlock all worksheets simultaneously. You can define the privileges that a User would have when the model is locked.

The UserForm I am referring to for the Password-Protection is a single "pop-up" window to ask for a password (See Below). You literally would just click the button that says "Administrative Rights" and the window pops up. You type a password and click "OK" to Lock all worksheets. When you wish to Unlock everything, you go through the exact same process. Since you've worked with UserForms before, this would be a 5-10 minute project for you.

I haven't tried it, but I'm pretty sure you could in theory create multiple Password Protected Profiles (i.e. "Protected for Senior Management" and "Protected for Manager"). So, if you were sending the model to Senior Management you would lock it using the "profile" that allows for unlocked cells to be edited, but still keeps locked cells protected and if you were sending it to Managers, you would use the "profile" that still allows them to use the "point and click macros", but doesn't allow them to edit anything. Would something like that work for you?

 

[Jim885]

Lexcon,
I'm interested. Looks like you put a lot of functionality into this.
Thanks, it should work.

 

[Lexcon07]

Hi Jim,

Great! Here are the details...

Insert a UserForm, I renamed mine "Administrative Rights". The following is what mine looks like, but you can obviously create yours differently if you would like.

Here are the "AdministrativeRights" UserForm Properties:

Here are the "OKButton" Properties

Here are the "PasswordBox" Properties

Here is the Code

In the UserForm

Private Sub OKButton_Click()
Dim wSheet As Worksheet
    Application.ScreenUpdating = False
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
            wSheet.Unprotect Password:=PasswordBox.Text
        Else
            wSheet.Protect Password:=PasswordBox.Text, DrawingObjects:=False, Contents:=True, Scenarios:= _
            True, AllowFormattingRows:=True, UserInterfaceOnly:=True, AllowFiltering:=True
        End If
    Next wSheet
    Application.ScreenUpdating = True
Unload Me
End Sub

In a VBA Module

Sub ShowPass()
    AdministrativeRights.Show
End Sub

I usually just insert a shape, label it "Administrative Rights" and assign the "ShowPass()" Macro to it.

This is the part of my UserForm Code where I define the "User Profile/Rights".

wSheet.Protect Password:=PasswordBox.Text, DrawingObjects:=False, Contents:=True, Scenarios:= True, AllowFormattingRows:=True, UserInterfaceOnly:=True, AllowFiltering:=True

Here are the options (If left out of your code, the "Default" applies):

• "DrawingObjects:=True" (Default) - To Protect Drawing Objects (i.e Shapes);
• "Contents:=True" - Protects Locked Cells
• "Scenarios:=True" (Default) - Protects Scenarios
• "AllowFormattingCells:=False" (Default) - If "True", allows Users to Format any Cell
• "AllowFormattingColumns:=False" (Default) - If "True", allows Users to Format any Columns
• "AllowFormattingRows:=False" (Default) - If "True", allows Users to Format any Rows
• "AllowInsertingColumns=False" (Default) - If "True", allows Users to Insert Columns
• "AllowInsertingRows=False" (Default) - If "True", allows Users to Insert Rows
• "AllowInsertingHyperLinks=False" (Default) - If "True", allows Users to Insert HyperLinks
• "AllowDeletingColumns=False" (Default) - If "True", allows Users to Delete Columns
• "AllowDeletingRows=False" (Default) - If "True", allows Users to Delete Rows
• "AllowSorting=False" (Default) - If "True", allows Users to Sort Data
• "AllowFiltering=False" (Default) - If "True", allows Users to Filter Data
• "AllowUsingPivotTables=False" (Default) - If "True", allows Users to Use Pivot Tables
• "UserInterfaceOnly:=False" (Default) - If "True", allows Users cannot edit the User Interface, but can Edit VBA Macros. If "False", both are protected

I think that's all of it...

I'm confident that if you go through this process twice, once for "Senior Management" and once for "Managers", and differentiate privileges between the two user group using the ProtectContents options listed above, that you will be ale to create two "Password-enabled" User Profiles. Use one to lock the file before sending to Senior Management and the other to lock the file before sending to Managers.

Hope this works out for you and please let me know how it goes or if you have any issues.

 

[Jim885]

Lexcon,

Very nice. I like the way the password is not specified, and can be changed at will whenever the sheets are protected.
Thank you for providing all the details and all the information.

 

[Lexcon07]

My pleasure.

I take it that means you got it to work? Were you able to create two profiles?

 

[Jim885]

It serves my purpose as is. Thanks again.
I'll also use this on other projects and then employ another level.
The CEO has already tried to strip this down to a basic spreadsheet with manual entries. I fully automated the previous process with code, but he went back to a primitive spreadsheet. Next he'll resort to a pencil with an eraser.
So gald I'm only doing consulting for this construction company. They even deny the COVID crisis. I'm even happier that I work from home.