VBA Code not Executing

[Domingoraine]

Hello, good day. I need help to execute my VBA code. I'm practically new in using VBA.
Here's the code I copied from another forum here.
Basically, what I want to do is auto stamp the username, date and time stamp on column L once they choose either rejected or approved on the drop down menu.
At the same time, I want to lock column L as well.
Is there a way to do this? And where do I put my VBA code, on the sheet itself or create another module?
Yesterday it worked, but then when I tried to lock column L, it suddenly stopped working.
Not sure, what's the cause of it.
Please help.
Thank you in advance.

Here's a screenshot from my excel sheet,VBA, and the code I used.

Private Sub Worksheet_Change(ByVal Target As Range)

' Define object variables
Dim statusRange As Range
Dim changedCell As Range

' Define variables
Dim currentUserName As String
Dim userNameColumn As String
Dim statusColumnNumber As Integer
Dim statusValuesList As Variant

' <<< Customize this >>>
Set statusRange = Range("K4:K100") ' Limit the cells that will record the status
statusValuesList = Array("Approved", "Rejected") ' Add more status values separated by commas and inside quotes (this is not case-sensitive)
userNameColumn = "L" ' Column letter where the UserName is going to be stamped

' Prevent from firing other events while making changes to cells
Application.EnableEvents = False

' Validate if cell changed belongs to valid column and rows and if it has a valid status
If Not Intersect(Target, statusRange) Is Nothing Then

For Each changedCell In Target

If Not IsError(Application.Match(changedCell.Value, statusValuesList, 0)) Then

' Get current username
currentUserName = Environ("Username")

Else

' Empty username string
currentUserName = vbNullString

End If

' Assign username to cell in previously defined column and same row
Range(userNameColumn & changedCell.Row).Value = currentUserName

Next changedCell

End If

' Reenable firing events
Application.EnableEvents = True

End Sub

 

[My Aswer Is This]

So, it looks like you want to use a sheet change event code.
So, if you enter certain values in column K you want to enter something in column L

It sounds like you want Username time and date in column L
It would be best to show me exactly how you want it to look
Time can be shown several different ways as can Date
And why the three choices

You have: Approved", "Rejected") ' Add more
So, what changes if approved is chosen versus Rejected

 

[Domingoraine]

Hello, thanks for your prompt reply.

Yes, I want to use a sheet change event code.
Yes, if the user choose either approved or rejected, I want the username, actual date and time stamp on Column L.

See sample below:

Column K is the drop down menu for either approved or rejected, Column L is for the approver's details:

So, it looks like you want to use a sheet change event code.
So, if you enter certain values in column K you want to enter something in column L

It sounds like you want Username time and date in column L
It would be best to show me exactly how you want it to look
Time can be shown several different ways as can Date
And why the three choices

You have: Approved", "Rejected") ' Add more
So, what changes if approved is chosen versus Rejected

 

[My Aswer Is This]

Hello, thanks for your prompt reply.

Yes, I want to use a sheet change event code.
Yes, if the user choose either approved or rejected, I want the username, actual date and time stamp on Column L.

See sample below:

Column K is the drop down menu for either approved or rejected, Column L is for the approver's details:

View attachment 78120

So what happens if they choose Rejected?

 

[Domingoraine]

So what happens if they choose Rejected?

Same result - auto add the username, date and time stamp. Basically, I just want the usernames, date and time stamp details on column L, whether they choose approved or rejected. Only 1 person will approve or reject it.

 

[My Aswer Is This]

Try this:
Modify Date and Time format as I do not understand yours

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/8/2022  1:03:48 AM  EST
If Target.Column = 11 Then

Select Case Target.Value
    Case "Approved", "Rejected"
        Target.Offset(, 1).Value = Application.UserName & "," & Now()
    End Select
End If
End Sub

 

[Domingoraine]

Try this:
Modify Date and Time format as I do not understand yours

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/8/2022  1:03:48 AM  EST
If Target.Column = 11 Then

Select Case Target.Value
    Case "Approved", "Rejected"
        Target.Offset(, 1).Value = Application.UserName & "," & Now()
    End Select
End If
End Sub

Hello, I tried adding these codes on the sheet code, but it didn't work. Apologies but I'm kinda new using macro.
am I supposed to just add the code on the sheet or add a module separately?

 

[My Aswer Is This]

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script runs when you enter the proper value in column 11 which is column K

This is not a module script

Your image of your sheet shows you had done this before.

 

[My Aswer Is This]

You will see in the code you must enter "Approved" or Rejected for the script to run.
Change this if you want to "Alpha" Or "Bravo" whatever you want

Remove the code you already have in the sheet

 

[Domingoraine]

Hello, I followed the exact procedure but it didn't work. I also tried testing it on another worksheet but also didnt work. I couldn't figure out what seems to be the problem.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script runs when you enter the proper value in column 11 which is column K

This is not a module script

Your image of your sheet shows you had done this before