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 -
JedidiaAsked:
Who is Participating?
 
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
 
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
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.

All Courses

From novice to tech pro — start learning today.