Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-13-2021, 02:48 AM
Marcia's Avatar
Marcia Marcia is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2019
Expert
Restrict editing by users in a shared workbook
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Attached Files
File Type: xlsm PollCount.xlsm (328.6 KB, 6 views)
Reply With Quote
  #2  
Old 10-13-2021, 08:15 AM
ArviLaanemets ArviLaanemets is offline Restrict editing by users in a shared workbook Windows 8 Restrict editing by users in a shared workbook Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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!
Reply With Quote
  #3  
Old 10-14-2021, 01:43 PM
Marcia's Avatar
Marcia Marcia is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2019
Expert
Restrict editing by users in a shared workbook
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #4  
Old 10-14-2021, 03:03 PM
Logit Logit is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Attached Files
File Type: xls Multiple User Sheet Protection.xls (46.5 KB, 7 views)
Reply With Quote
  #5  
Old 10-15-2021, 05:14 PM
Marcia's Avatar
Marcia Marcia is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2019
Expert
Restrict editing by users in a shared workbook
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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?
Reply With Quote
  #6  
Old 10-15-2021, 07:19 PM
Logit Logit is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

The sheet in question does not exist.
Reply With Quote
  #7  
Old 10-15-2021, 07:52 PM
Marcia's Avatar
Marcia Marcia is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2019
Expert
Restrict editing by users in a shared workbook
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Logit View Post
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.
Reply With Quote
  #8  
Old 10-16-2021, 06:49 AM
Logit Logit is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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
Attached Files
File Type: xls Multi User Logins.xls (90.0 KB, 8 views)
Reply With Quote
  #9  
Old 10-19-2021, 07:31 AM
Marcia's Avatar
Marcia Marcia is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2019
Expert
Restrict editing by users in a shared workbook
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #10  
Old 10-20-2021, 11:39 AM
Marcia's Avatar
Marcia Marcia is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2019
Expert
Restrict editing by users in a shared workbook
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #11  
Old 10-20-2021, 01:46 PM
Logit Logit is offline Restrict editing by users in a shared workbook Windows 10 Restrict editing by users in a shared workbook Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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

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 editing by users in a shared workbook Restrict ppt from editing wardma PowerPoint 3 05-02-2017 12:19 PM
Restrict editing by users in a shared workbook Could not remove Restrict editing markecb Word 4 02-18-2016 07:22 AM
Restrict editing by users in a shared workbook Restrict formatting and editing problem David141 Word 1 08-28-2014 05:18 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft