Results 1 to 3 of 3
  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

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Hi Che,

    Replace your code in the worksheet module of Log Sheet with the modified code below. When a date is placed in column C on the Log sheet, the name in the named Range "CurrentUser" will be placed on the same line as the date and the adjacent cell to the right stamped with the current date and time.

    HTH,
    Maud

    Place in Log sheet worksheet module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        On Error GoTo ErrHandler
        With Worksheets("Login")
        If Not Intersect(Target, Range("C:C")) Is Nothing Then
            Application.EnableEvents = False
            Cells(Target.Row, "V") = .Range("CurrentUser")
            Cells(Target.Row, "W") = Now()
        End If
        End With
        Application.EnableEvents = True
        Exit Sub
    ErrHandler:
        MsgBox Err.Description
        Application.EnableEvents = True
    End Sub

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi CHE

    ..use Maud's code, OR change the line in your code from..
    rCell.Offset(0, 19).Value = UserName()
    to..
    rCell.Offset(0, 19).Value = [CurrentUser]

    zeddy
    Passenger Navigation Aide
    Last edited by zeddy; 2015-07-07 at 17:33.

Posting Permissions

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