Results 1 to 1 of 1
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Post VBA Code to Get Value from A Cell in Another Worksheet

    I am trying to get the value from Q8 (Defined name: CurrentUser) from the Login worksheet and copy into column V every time a timestamp is added in column W.
    The current code that I am using adds a timestamp and gets the user logged onto Windows, but instead I would like get the user name that gets logged in on the Login in worksheet. Is there a way to accomplish this by modifying this code or any other VBA method? See attachment for an example.
    [CODE][Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("C:C"))
    If Not rChange Is Nothing Then
    Application.EnableEvents = False
    For Each rCell In rChange
    If rCell > "" Then
    rCell.Offset(0, 19).Value = UserName()
    rCell.Offset(0, 20).Value = Date & " " & Time()
    Else
    rCell.Offset(0, 1).Clear
    End If
    Next
    End If
    ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub
    Public Function UserName()
    UserName = Environ$("UserName")
    End Function

    /CODE]
    I greatly appreciate everyone help and time.
    Attached Files Attached Files

Posting Permissions

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