Results 1 to 2 of 2

User Stamp when Modifying a Cell

Threaded View

  1. #1
    Registered User
    Join Date
    02-20-2012
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2010
    Posts
    30

    User Stamp when Modifying a Cell

    I am trying to make it so if any cell is modified in ranges D12:D20, D22:D25, D27:D37 or D39:D45, a user stamp appears in the column beside it, I have the following code so far, but it will only work for the first set of ranges, I am pretty sure its because I have set to " Is Nothing Then Exit Sub" after the first "If" statement, but I dont know what the correct way is to make this work, it should be pretty simple. Kinda new at this Excel VBA thing so bear with me. Thank you!

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D12:D20")) Is Nothing Then Exit Sub
    Sheets("Sheet2").Unprotect
    Application.EnableEvents = False
    With Target
        .Offset(, 1).Value = Application.UserName
    End With
    Sheets("Sheet2").Protect
    Application.EnableEvents = True
    
    If Intersect(Target, Range("D22:D25")) Is Nothing Then Exit Sub
    Sheets("Sheet2").Unprotect
    Application.EnableEvents = False
    With Target
        .Offset(, 1).Value = Application.UserName
    End With
    Sheets("Sheet2").Protect
    Application.EnableEvents = True
    
    
    If Intersect(Target, Range("D27:D37")) Is Nothing Then Exit Sub
    Sheets("Sheet2").Unprotect
    Application.EnableEvents = False
    With Target
        .Offset(, 1).Value = Application.UserName
    End With
    Sheets("Sheet2").Protect
    Application.EnableEvents = True
    
    If Intersect(Target, Range("D39:D45")) Is Nothing Then Exit Sub
    Sheets("Sheet2").Unprotect
    Application.EnableEvents = False
    With Target
        .Offset(, 1).Value = Application.UserName
    End With
    Sheets("Sheet2").Protect
    Application.EnableEvents = True
    End Sub

    Solved, Thank you Paul!

    Quote Originally Posted by Paul View Post
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D12:D20, D22:D25, D27:D37, D39:D45")) Is Nothing Then
        Application.EnableEvents = False
        Sheets("Sheet2").Unprotect
        Target.Offset(, 1).Value = Environ("Username")
        Sheets("Sheet2").Protect
        Application.EnableEvents = True
    End If
    End Sub
    Last edited by cg0789; 02-28-2012 at 01:20 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1