Solved

EXCEL VBA

Posted on 2012-04-05
2
251 Views
Last Modified: 2012-04-19
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
Comment
Question by:Jedidia
2 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 37812894
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
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 500 total points
ID: 37812957
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Hiding column macro 10 28
Message Logon Windows 2012 R2 Server 16 15
Filling Blank Cells 14 20
Input Data Using a unique ID from another sheet in Excel 3 17
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now