Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

EXCEL VBA

Posted on 2012-04-05
2
Medium Priority
?
311 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Loops Section Overview

885 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