Solved

EXCEL VBA

Posted on 2012-04-05
2
295 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Desktop in File-open on Excel 7 34
Checking references in VBA 3 23
Tricky Shapes formula part 4 4 13
Excel with two IF statements 3 7
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

749 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