#1
|
||||
|
||||
Restrict editing by users in a shared workbook
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. |
#2
|
|||
|
|||
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! |
#3
|
||||
|
||||
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. |
#4
|
|||
|
|||
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 |
#5
|
||||
|
||||
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? |
#6
|
|||
|
|||
The sheet in question does not exist.
|
#7
|
||||
|
||||
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.
|
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
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.
|
#10
|
||||
|
||||
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.
|
#11
|
|||
|
|||
I am so sorry it does not work online. I've not used the online features.
|
Thread Tools | |
Display Modes | |
|
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 |