Restrict editing by users in a shared workbook

Marcia · #1 10-13-2021, 02:48 AM

Hi. I have prepared the attached workbook that will quickly tally the ballots in the national elections in May next year in our small town. I know it is still early but the filing of candidacy had ended last week and the names of the local candidates are already known. The workbook shall be shared among friends for our consumption (unofficial).
My problems are:
1. The sheets (15 sheets) contain lots of formulae but when the workbook is shared, the sheets must be unprotected so the other users could input their respective data in their assigned sheets. There is the risk of accidentally deleting the formulae or the names and positions of the candidates. Ideally, the users with whom the workbook was shared for data input must enter in the cells with white background only.
2. Because the sheets are unprotected, a user with editing authorization could open another sheet other than the sheet that which was assigned to him/her to update. I would like each user to view but not edit other sheets.
The only workaround that I could think of solving number 1 is to hide the Totals columns and all other columns filled with formulae. For number 2, I might just leave it to providence

.
Any better solution would be very much appreciated.
Thank you.

ArviLaanemets · #2 10-13-2021, 08:15 AM

When you protect the sheet, you can set different conditions to different areas of sheet, e.g. allow for some columns users to edit data, to add new data, etc, (I think the you can set all this from rightmost tab of format menu), and for some columns (e.g. columns with formulas) to restrict editing at all. Also you can there prevent users from seeing formulas in sheet or in certain ranges on sheet- the formula bar will display the value instead of formula.

Also from VBA Project you can set sheets with sensitive info, you don't want to share, to be "Very Hidden". Such sheets aren't displayed when user right-clicks on sheet tab and selects the option to hide/unhide sheets - they can be made visible from VBA project only! And when you protect the VBA Project itself with password, unless the user is an experienced hacker, the access to such sheets is effectively blocked. Put your sensitive info on table in Very Hidden sheet, restrict seeing formulas on any sheet for user, and users don't even have a clue that such sheet exists or how it is named!

Marcia · #3 10-14-2021, 01:43 PM

The locked columns and cells turn unprotected when the other users receive their copy.
I also tried the very hidden sheets and gave the users the passwords but the developer is not available in their end. There is no option to add it so they cannot unhide their own sheets.

Logit · #4 10-14-2021, 03:03 PM

Code:

Option Explicit

Private Sub Cancel_Click()

    Unload Me

End Sub

Private Sub OK_Click()

Dim c               As Object
Dim UserName        As String
Dim Password        As String
Dim MyError         As Integer
    
    'Assign UserName to a variable
    UserName = UserVerification.UserName.Text
    
    'Assign Password to a variable
    Password = UserVerification.Password.Text
    
    'Specify the sheet and range to look up the UserName
    With Sheets("UserNames").Range("A:A")
        Set c = .Find(What:=UserName, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
    End With
    
    'If UserName cannot be found
    If c Is Nothing Then
        MsgBox "The username you have inputted is invalid, please try again.", vbCritical, "Incorrect UserName"
        Exit Sub
    End If
    
    'If Password is correct
    If Password = Sheets("UserNames").Range(c.Address).Offset(0, 1).Text Then
        ThisWorkbook.Unprotect Password:="Password"
                
        'If the sheet to make visible is not in the workbook an error will occur
        'If there is no error then make that sheet visible
        On Error Resume Next
        Sheets(Range(c.Address).Offset(0, 2).Text).Visible = True
        MyError = Err
        On Error GoTo 0
        ThisWorkbook.Protect Password:="Password", Structure:=True
        
        'If there was an error let the user know to try again.
        If MyError <> 0 Then
            MsgBox "The sheet specified could not be shown, please contact your system administrator.", vbCritical, "Sheet Specification Error"
            Exit Sub
        End If
        Else
        
        'If the password is incorrect let the user know to try again
        MsgBox "The password you have specified is incorrect, please try again.", vbCritical, "Incorrect Password"
        Exit Sub
    End If
    
    'Dismiss the userform
    Unload Me

End Sub

Private Sub UserForm_Initialize()
    Me.UserName.Value = "User"
End Sub

Marcia · #5 10-15-2021, 05:14 PM

Thank you Logit for the code. According to my search, co-authoring does not support macros but I copied the code above to my workbook since I might end up sharing the file in the conventional way, not through Excel Web.
I got an error message, "Unable to set the Visible property of the Worksheet class". The workbook is not protected. What might be causing the error?

Logit · #6 10-15-2021, 07:19 PM

The sheet in question does not exist.

Marcia · #7 10-15-2021, 07:52 PM

Quote:

Originally Posted by Logit

The sheet in question does not exist.

It does but I made the sheet very hidden through the vba properties window. When I ran the macro to open a visible sheet, there was no error.

Logit · #8 10-16-2021, 06:49 AM

Admittedly that project is somewhat restrictive.

Here is another project you might find more amenable :

Code:

Option Explicit

Dim HFD As Integer, HFR As Integer
Dim N As Long, F As Long, Pass As String

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("SetUp").Visible = xlSheetVisible
    For N = 3 To HFR
        If ComboBox1.Value = Sheets("SetUp").Cells(15, N).Value Then
            Exit For
        End If
    Next N
    
    If TextBox1.Value = Sheets("SetUp").Cells(16, N).Value Then
    Sheets("SetUp").Visible = xlSheetVeryHidden
    MsgBox Range("SetUp!C10").Value, , Range("SetUp!C9").Value & " " & Sheets("SetUp").Cells(15, N).Value
    Unload UserForm1
    Sheets("SetUp").Visible = xlSheetVisible
    Pass = Sheets("SetUp").Range("K12").Value
    Sheets("SetUp").Visible = xlSheetVeryHidden
    
        For F = 17 To HFD
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "X" Then
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
            End If
            
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "P" Then
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Protect Password:=Pass
            End If
        Next F
        
    Else
    
        MsgBox Range("SetUp!C6").Value, , Range("SetUp!C7").Value
        TextBox1.Value = ""
        Sheets("SetUp").Visible = xlSheetVeryHidden
    End If
    
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
    Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
Dim WkSht As Worksheet
Application.ScreenUpdating = False

    For Each WkSht In Worksheets
        If Not WkSht.Name = "Intro" Then WkSht.Visible = xlSheetVeryHidden
    Next WkSht
        Sheets("SetUp").Visible = xlSheetVisible
        HFD = Sheets("SetUp").Range("B65536").End(xlUp).Row
        HFR = Sheets("SetUp").Range("IV15").End(xlToLeft).Column
        UserForm1.Caption = Range("SetUp!C3").Value
        Label3.Caption = Range("SetUp!C4").Value
    For N = 3 To HFR
        With ComboBox1
            .AddItem Sheets("SetUp").Cells(15, N).Value
        End With
    Next N
    
Sheets("SetUp").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Marcia · #9 10-19-2021, 07:31 AM

Thank you Logit. A nasty bug tied me in bed the last few days. There's a long list of readme, but I hope I can follow every instruction. I will get back when I'm done or if I find I am not doing a step the right way.

Marcia · #10 10-20-2021, 11:39 AM

The code in #8 works great. I tried it on my actual workbook and what a joy, but when I shared it online, it says "activex controls do not... in Excel for the web." Sigh.

Logit · #11 10-20-2021, 01:46 PM

I am so sorry it does not work online. I've not used the online features.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to restrict others from viewing PAST events for shared calendar?	dylansmith	Outlook	1	10-17-2018 05:03 AM
Restrict ppt from editing	wardma	PowerPoint	3	05-02-2017 12:19 PM
Could not remove Restrict editing	markecb	Word	4	02-18-2016 07:22 AM
Restrict formatting and editing problem	David141	Word	1	08-28-2014 05:18 AM