Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Restricting excel cell editing

    Hi,

    I have a spreadsheet that I want to allow everyone to edit but certain cells I want to restrict to only some users, is there a easy way of doing this?

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    You can unprotect all cells on the sheet>protect the cells desired with a password>give the pw to those desired. You must then assume that all attended Texas A & M because sheet protection is very easy to defeat.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you are on a network you may be able to restrict access to particular users or groups using Review - allow users to edit ranges.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Madhatter,

    The following workbook will allow only certain users to access certain cells with out using a sheet being password protected. When the user opens the workbook, they will be prompted for their credentials. if they are correct, then they will be able to change the values in B2:B6. If the credentials entered are not authorized, they will not be able to select the cells to change them. A measure is in place to prevent the worksheet from being opened with macros disabled. Of course, to prevent the user from changing the code, the VB project must be locked with a password which is not easily defeated. Working credentials for this sample are:

    Username: jbarret Password: bulldogs
    Username: bhelmold Password: 34crimson54

    The number of authenticated users and protected cells are unlimited and easily adjusted. Windows authentication could also be used if the users are on a network.

    HTH,
    Maud

    Update: Revised code to prevent user from highlighting more than one cell them right clicking and selecting clear contents.
    Attached Files Attached Files
    Last edited by Maudibe; 2015-04-23 at 23:27. Reason: Revised file

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    I like your method! But, if you copy a block of cells, e.g. [k2 : p8], you could then paste that block to cell [a2], thus replacing your 'protected' cells in column [B].
    So, a little more coding (but not impossible) needed to prevent that.

    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-04-24)

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Again, thinking outside the box Zeddy!

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    I'd love to look at your code but no PW?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    Maybe Maud is busy, but I'm sure he won't mind if I post an unprotected version of the file for you to look at.
    Maud's code is short, sweet, and effective. As always.

    zeddy
    Attached Files Attached Files

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    Very nice trick hiding the main sheet to detect if macros are enabled!
    You can bet this one is going into my bag of tricks!

    If I might make a suggestion to simplify the authorization process how about killing the form and using:
    Code:
    Option Explicit
    
    Public Function CheckCredentials() As Boolean
    
       Dim vAuthUsers()  As Variant
       Dim zLoggedUser   As String
       Dim iCntr         As Integer
    
       On Error Resume Next
    
       vAuthUsers = Array("jbarret", "bhelmold")
       zLoggedUser = Environ("username")
       
       For iCntr = 0 To UBound(vAuthUsers)
           If vAuthUsers(iCntr) = zLoggedUser Then
            CheckCredentials = True
            Exit Function
          Else
            CheckCredentials = False
          End If
       Next iCntr
       
    End Function
    Since the user is already logged on why bother to reauthorize?

    Also I thought this might be a little more efficient.
    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
       Dim bCredentials As Boolean
       
       bCredentials = CheckCredentials()
       
       On Error Resume Next
    
       If Target.Cells.Count > 1 And _
          Not Intersect(Target, Range("B2:B6")) Is Nothing _
          And bCredentials Then
         Range("A1").Select
        Exit Sub
       End If
    
       If Target.Cells.Count > 1 And Not Intersect(Target, Range("B2:B6")) Is Nothing And _
         bCredentials Then
         Exit Sub
       End If
    
       If Not Intersect(Target, Range("B2:B6")) Is Nothing Then
         If Not bCredentials Then Target.Offset(0, 1).Select
       End If
       
    End Sub
    Test File: Credentials_Revised3-unprotected.xlsm

    Zeddy, thanks for the unlocked version!

    HTH
    Last edited by RetiredGeek; 2015-04-24 at 13:58.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2015-04-24)

  12. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Windows authentication could also be used if the users are on a network.
    RG,

    Thanks for showing the method if the workbook is Windows Authenticated. But if they do not have user accounts or if they are logged on a generic or someone else's windows account, then they can use my method.

    Since the user is already logged on why bother to reauthorize?
    If the user enters the incorrect credentials or just closes the form at startup, there must a way to re-enter their credentials at any point, hence, the credentials button.

    Zeddy,

    Here is the added code to prevent pasting over a protected cell
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("B2:B6, G14, E21")
    If Not Intersect(Selection, rng) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
    End Sub
    PS. Unlocked
    Attached Files Attached Files

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-04-24)

  14. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    My comment about "reauthorization" wasn't aimed at your button but rather my method of authorization.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by RetiredGeek View Post
    Maud,
    Very nice trick hiding the main sheet to detect if macros are enabled!
    You can bet this one is going into my bag of tricks!
    As I was adding Maud's code to said bag it occurred to me what if there were more than one sheet to protect so I decided to build on Maud's fine code once again, he always gives me so many Ideas!

    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
       
       Dim sht As Worksheet
       
       Application.ScreenUpdating = False
       
       Sheets("Warning").Visible = True '*** One Sheet MUST be Visible! ***
       
       For Each sht In ActiveWorkbook.Sheets
       
          If sht.Name = "Warning" Then
          
          Else
            sht.Visible = xlSheetVeryHidden
          End If
          
       Next sht
       
       
       ThisWorkbook.Save
       Application.ScreenUpdating = True
       
    End Sub       'Workbook_BeforeClose()
    
    Private Sub Workbook_Open()
    
       Dim sht As Worksheet
    
       Application.ScreenUpdating = False
       For Each sht In ActiveWorkbook.Sheets
       
          If sht.Name = "Warning" Then
          Else
            sht.Visible = True
          End If
          
       Next sht
       
       Sheets("Warning").Visible = xlSheetVeryHidden
       
       Application.ScreenUpdating = True
       
    End Sub       'Workbook_Open()
    Hope someone finds this useful.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2015-04-25)

  17. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Well done RG!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •