Insert username in a cell, based on cell value

[Miltoft]

Hi everyone

How am I able to insert the username in the column "Closed by" and the actual date in column "Date for closing Claim Internal"?

I tried with this VBA code, but it only take line 31.

Private Sub Closed_by()

If Range("AJ31") = "Closed" Then
Range("AK31").Value = Application.UserName

End If
End Sub

 

[Joe4]

So it sounds like you want automated VBA code to run when a cell in column AJ is updated to "Closed", right?

If so, then this code should do what you want, but it NEEDS to be put in the sheet module.
An easy way to ensure this is to go to the sheet that you want to apply it to, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if column AJ updated to "Closed" after row 2
    If (Target.Column = 36) And (Target.Row >= 2) And (Target.Value = "Closed") Then
        Application.EnableEvents = False
'       Update columns AK and AL
        Target.Offset(0, 1) = Application.UserName
        Target.Offset(0, 2) = Date
        Application.EnableEvents = False
    End If
    
End Sub

 

[Miltoft]

So it sounds like you want automated VBA code to run when a cell in column AJ is updated to "Closed", right?

If so, then this code should do what you want, but it NEEDS to be put in the sheet module.
An easy way to ensure this is to go to the sheet that you want to apply it to, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if column AJ updated to "Closed" after row 2
    If (Target.Column = 36) And (Target.Row >= 2) And (Target.Value = "Closed") Then
        Application.EnableEvents = False
'       Update columns AK and AL
        Target.Offset(0, 1) = Application.UserName
        Target.Offset(0, 2) = Date
        Application.EnableEvents = False
    End If
   
End Sub

Thank you so much for your help. It works, but only in the first line. The other rows return with this statement: #NAME? Does it require a For each.... line or?

 

[Joe4]

Oh geez, I have a typo. It should be this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if column AJ updated to "Closed" after row 2
    If (Target.Column = 36) And (Target.Row >= 2) And (Target.Value = "Closed") Then
        Application.EnableEvents = False
'       Update columns AK and AL
        Target.Offset(0, 1) = Application.UserName
        Target.Offset(0, 2) = Date
        Application.EnableEvents = True
    End If
   
End Sub

You probably first need to manually turn events back on so they will work again.
You can do that by running this little macro manually:

Sub ReEnableEvents()
        Application.EnableEvents = True
End Sub

 

[Miltoft]

Oh geez, I have a typo. It should be this:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
  
'   See if column AJ updated to "Closed" after row 2
    If (Target.Column = 36) And (Target.Row >= 2) And (Target.Value = "Closed") Then
        Application.EnableEvents = False
'       Update columns AK and AL
        Target.Offset(0, 1) = Application.UserName
        Target.Offset(0, 2) = Date
        Application.EnableEvents = True
    End If
  
End Sub

You probably first need to manually turn events back on so they will work again.
You can do that by running this little macro manually:

Sub ReEnableEvents()
        Application.EnableEvents = True
End Sub

Brilliant :D Thank you so much.

 

[Joe4]

You are welcome.

Note when marking posts as the solution, you want to mark the original post that actually contains the solution (not the post acknowledging that it worked).
I have updated that for you.