Insert username in a cell, based on cell value

Miltoft

New Member
Joined
May 26, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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


Screenshot 2022-02-09 134634.jpg
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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:
VBA Code:
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
 
Upvote 0
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:
VBA Code:
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?
 
Upvote 0
Oh geez, I have a typo. It should be this:
Rich (BB code):
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:
VBA Code:
Sub ReEnableEvents()
        Application.EnableEvents = True
End Sub
 
Upvote 0
Solution
Oh geez, I have a typo. It should be this:
Rich (BB code):
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:
VBA Code:
Sub ReEnableEvents()
        Application.EnableEvents = True
End Sub
Brilliant :D Thank you so much.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,628
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top