CODEWITHSUNDEEP
excelvba
Mastan Shaik
Mastan Shaik

Reputation: 5

Need help for Auto user name and time

I am using an Excel in which I need to get Auto user name and Time for two entry's example 1. If I insert a value in column 2 I need user name and time at cell "A" and "O" 2. In same Excel sheet again for the entry at Column(7) I need date and Username at cell "I" and "N"

My code working for single point and I am confuse how to use twice.

Check the code I try and advise

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, c As Range
    Set rng = Application.Intersect(Target, Me.Columns(2))
    If rng Is Nothing Then Exit Sub
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            If Len(c.Offset(0, -1).Value) = 0 Then
                With c.EntireRow
                    .Cells(1, "A").Value = Now()
                    '.Cells(1, "B").Value = Date
                    .Cells(1, "O").Value = Environ("username")
                End With
            End If
        End If

    Set rng = Application.Intersect(Target, Me.Columns(7))
    If rng Is Nothing Then Exit Sub
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            If Len(c.Offset(0, -1).Value) = 0 Then
                With c.EntireRow
                    .Cells(1, "I").Value = Now()
                    '.Cells(1, "B").Value = Date
                    .Cells(1, "N").Value = Environ("username")
                End With
            End If
        End If
    Next c
End Sub

Upvotes: 0

Views: 71

Answers (2)

Damian
Damian

Reputation: 5174

If I got it right, this is working for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        If Me.Cells(Target.Row, 1) = vbNullString Then Me.Cells(Target.Row, 1) = Now()
        If Me.Cells(Target.Row, 15) = vbNullString Then Me.Cells(Target.Row, 15) = Environ("username")
    ElseIf Target.Column = 7 Then
        If Me.Cells(Target.Row, 9) = vbNullString Then Me.Cells(Target.Row, 9) = Now()
        If Me.Cells(Target.Row, 14) = vbNullString Then Me.Cells(Target.Row, 14) = Environ("username")
    End If

End Sub

Thought I'm not sure if you want the logging to be on the same row you are inserting data or rewrite it always on the same cell.

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57743

The problematic line is If rng Is Nothing Then Exit Sub because if Target is not in column 2 it exits sub and will never reach the second test for column 7.

So use If Not rng Is Nothing Then instead:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, c As Range
    Set rng = Application.Intersect(Target, Me.Columns(2))
    If Not rng Is Nothing Then
        For Each c In rng.Cells
            If Len(c.Value) > 0 Then
                If Len(c.Offset(0, -1).Value) = 0 Then
                    With c.EntireRow
                        .Cells(1, "A").Value = Now()
                        '.Cells(1, "B").Value = Date
                        .Cells(1, "O").Value = Environ("username")
                    End With
                End If
            End If
        Next c
    End If

    Set rng = Application.Intersect(Target, Me.Columns(7))
    If Not rng Is Nothing Then
        For Each c In rng.Cells
            If Len(c.Value) > 0 Then
                If Len(c.Offset(0, -1).Value) = 0 Then
                    With c.EntireRow
                        .Cells(1, "I").Value = Now()
                        '.Cells(1, "B").Value = Date
                        .Cells(1, "N").Value = Environ("username")
                    End With
                End If
            End If
        Next c
    End If
End Sub

Upvotes: 1

Related Questions