• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

EXCEL VBA

Is there a way to code a VBA script to detect a users login ID (userID = LCase(Environ$("username"))) and based on the result lock the active cell from being edited?

Basically if user A, B, & C clicks in a specific cell and makes changes no problem.
If user D or E tries, it prevents the edit and displays a little "You don't have access" message?

Can this be done strickly with VBA code?  If so, can you help with examples.

Thanks -
0
Jedidia
Asked:
Jedidia
1 Solution
 
Michael VasilevskySolutions ArchitectCommented:
Sure, something like (to lock cell A1 depending on user):

Private Sub Workbook_Open()
     dim sUser as string

     sUser = LCase(Environ$("username"))) 
     Select case sUser
          Case is = "Admin"
               cells("A1").locked = false
          Case else
               cells("A1").locked = True
     End Select
End Sub

Open in new window

0
 
Anthony BerenguelCommented:
try this, you will have to modify your selection structure as needed. Also, this code goes in the worksheet object.

Dim activeCellAddress As String
Dim activeCellValue As Variant
Dim skipWorksheetChangeEvent As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim userLogin As String
    userLogin = LCase(Environ$("username"))
    
    If skipWorksheetChangeEvent = False Then
        If userLogin = "a user login that isn't allowed to make changes" Then
            skipWorksheetChangeEvent = True
            MsgBox "You are not allowed to change this cell"
            Range(activeCellAddress).Value = activeCellValue
        End If
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    activeCellAddress = activeCell.Address
    activeCellValue = activeCell.Value
    skipWorksheetChangeEvent = False
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now