Solved

How to use VBA to ensure "Read-Only" access to excel doesn't allow editing/saving as a copy

Posted on 2015-02-04
13
226 Views
Last Modified: 2016-02-11
I have a shared Excel file for a client in which many employees have to view, but only a limited few can actually edit it.

In that case, I decided to do the basic protection in the saving general settings. This asked for a password when opening the file or gave the option for "read-only." When opened in "read-only," you were still allowed to edit the file and save it as a copy. In that case, everyone would just edit it and save copies all over the server.

My next step was to use a macros in Excel 2010 VBA, in which requested a password when saving the file. This worked great but in order to work, you need to "enable macros."

Then I decided to find a macros that would essentially force users to enable their macros. This would show a sheet requesting the user to enable their macros before they could see the other sheets in the workbook. This worked, but when exiting the workbook, it would consider enabling the macros as an edit, requesting a save. The problem is it wants a password to save and I am not sure why.

I started to develop conflict and need a point in the correct direction. I am not sure whether or not there is an easier way to go about this then some macros in the VBA.

The following is the code I recently had in my workbook.

Option Explicit
 
Private Sub Workbook_Open()
     
    With Application
         'disable the ESC key
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
         
        Call UnhideSheets
         
        .ScreenUpdating = True
         're-enable ESC key
        .EnableCancelKey = xlInterrupt
    End With
     
End Sub
 '
Private Sub UnhideSheets()
     '
    Dim Sheet As Object
     '
    For Each Sheet In Sheets
        If Not Sheet.Name = "Prompt" Then
            Sheet.Visible = xlSheetVisible
        End If
    Next
     '
    Sheets("Prompt").Visible = xlSheetVeryHidden
     '
    Application.Goto Worksheets(1).[A1], True '< Optional
     '
    Set Sheet = Nothing
    ActiveWorkbook.Saved = True
     
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
         
        Call HideSheets
         
        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With
End Sub
 
Private Sub HideSheets()
     '
    Dim Sheet As Object '< Includes worksheets and chartsheets
     '
    With Sheets("Prompt")
         '
         'the hiding of the sheets constitutes a change that generates
         'an automatic "Save?" prompt, so IF the book has already
         'been saved prior to this point, the next line and the lines
         'relating to .[A100] below bypass the "Save?" dialog...
        If ThisWorkbook.Saved = True Then .[A100] = "Saved"
         '
        .Visible = xlSheetVisible
         '
        For Each Sheet In Sheets
            If Not Sheet.Name = "Prompt" Then
                Sheet.Visible = xlSheetVeryHidden
            End If
        Next
         '
        If .[A100] = "Saved" Then
            .[A100].ClearContents
            ThisWorkbook.Save
        End If
         '
        Set Sheet = Nothing
    End With
     '
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Password As String
Dim EnteredPassword As String

Password = "password1"
EnteredPassword = InputBox("Enter password to save changes")
If EnteredPassword <> Password Then
Cancel = True
MsgBox ("Password incorrect, file not saved")
End If

End Sub

Open in new window


I have attached the "test" Excel 2010 file that I am experimenting this with. All of the passwords are "password1". I would appreciate any help available. Thank you.

Michael
test.xlsm
0
Comment
Question by:Larrydz
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40590389
Quick tip: You can prevent the events from running at will by :
Application.EnableEvents = False
Do not forget to set it back to true when you are done however!
ALternatively:
http://www.jkp-ads.com/Articles/NoEvents00.asp
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40590908
Have you considered replacing their normal File menu with a menu that doesn't include Save and Save As items?

Are you concerned with the ability of someone to create a copy if the data via copy/paste?
0
 

Author Comment

by:Larrydz
ID: 40591328
There are a number of employees that must gain editing abilities to this file daily. As per the copy/paste ability I am not to worried about. I just need to get this client a more secure workbook to ensure restricted users do not decide to change some data around. If there is a successful way to make this file a "read-only" without the ability to save it in any way shape or form, that would be fine. This would be as long as there is a password to allow certain users to edit and save it.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40591369
The removal action of the Save and Save As menu items would only apply to those people who are NOT allowed to save.  Those people with rights to save would see the Save/Save As menu items.
0
 

Author Comment

by:Larrydz
ID: 40591495
aikimark, by any chance could you clarify what you mean by removing the save and save as menu items? I know this could be done easily via VBA but don't understand how it would be available to the people with rights to actually edit and save it. Thank you for your help.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40591527
As part of the actions you take when the workbook is opened, you might remove/hide/disable the menu items.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Larrydz
ID: 40599065
For my issue, I have not been delivered an acceptable solution and was wondering if there were any other options/clarifications out there. Thank you for your help.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40599068
click the request attention link and ask for more expert participation.
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40600085
I would do it like so:

Option Explicit
 
Private Sub Workbook_Open()
     
    With Application
         'disable the ESC key
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False
         
        Call UnhideSheets
         
        .ScreenUpdating = True
         're-enable ESC key
        .EnableCancelKey = xlInterrupt
    End With
     
End Sub
 '
Private Sub UnhideSheets()
     '
    Dim Sheet As Object
     '
    For Each Sheet In Sheets
        If Not Sheet.Name = "Prompt" Then
            Sheet.Visible = xlSheetVisible
        End If
    Next
     '
    Sheets("Prompt").Visible = xlSheetVeryHidden
     '
    Application.Goto Worksheets(1).[A1], True '< Optional
     '
    Set Sheet = Nothing
    ActiveWorkbook.Saved = True
     
End Sub
 
 
Private Sub HideSheets()
     '
    Dim Sheet As Object '< Includes worksheets and chartsheets
     '
    With Sheets("Prompt")
         '
         'the hiding of the sheets constitutes a change that generates
         'an automatic "Save?" prompt, so IF the book has already
         'been saved prior to this point, the next line and the lines
         'relating to .[A100] below bypass the "Save?" dialog...
        If ThisWorkbook.Saved = True Then .[A100] = "Saved"
         '
        .Visible = xlSheetVisible
         '
        For Each Sheet In Sheets
            If Not Sheet.Name = "Prompt" Then
                Sheet.Visible = xlSheetVeryHidden
            End If
        Next
         '
        If .[A100] = "Saved" Then
            .[A100].ClearContents
            ThisWorkbook.Save
        End If
         '
        Set Sheet = Nothing
    End With
     '
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Password As String
    Dim EnteredPassword As String

    Password = "password1"
    EnteredPassword = InputBox("Enter password to save changes")
    If EnteredPassword <> Password Then
        Cancel = True
        MsgBox ("Password incorrect, file not saved")
    Else
        HideSheets
        'Make sure sheets are unhidden when save is done
        Application.OnTime Now, "Thisworkbook.Unhidesheets"
    End If
End Sub

Open in new window

0
 

Author Comment

by:Larrydz
ID: 40608453
Thank you for your response jkpieterse, but I am getting an error at the "Sheet Visible = xl SheetVeryHidden"

For Each Sheet In Sheets
            If Not Sheet.Name = "Prompt" Then
            Sheet.Visible = xlSheetVeryHidden
            End If

Open in new window

0
 
LVL 12

Expert Comment

by:FarWest
ID: 40644689
is it acceptable for you/your client to embed this excel file within another Executable?
0
 

Accepted Solution

by:
Larrydz earned 0 total points
ID: 40674268
My current configuration is working for the client, and if any other issues arrive I will be sure to use Experts Exchange for more information. Thank you to anyone who has posted a potential solution.
0
 

Author Closing Comment

by:Larrydz
ID: 40681930
My current configuration is working
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now