VBA Code not Executing

Domingoraine

New Member
Joined
Nov 7, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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.

1667882548369.png


1667882635559.png




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
 
Not sure what you mean by what format

In a earlier post you said:
Yesterday it worked, but then when I tried to lock column L, it suddenly stopped working.

Maybe you still have column K locked.
Not exactly sure how you did that.
I never lock columns.
I suggest you create a new workbook and try using the code in that workbook and see what happens.
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Not sure what you mean by what format

In a earlier post you said:
Yesterday it worked, but then when I tried to lock column L, it suddenly stopped working.

Maybe you still have column K locked.
Not exactly sure how you did that.
I never lock columns.
I suggest you create a new workbook and try using the code in that workbook and see what happens.
Hello, I tried closing my excel and added the code and voila it worked! Thank you very much for your help.
 
Upvote 0
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
Hello, is there a way to lock a particular column with an existing VBA code?
Here's my code below. This adds the value on Column P when Column O says either Approved or Rejected.
Now, I would like to protect column P and make sure no one copies the values.
I want Column P to be protected and make sure no other user adds details in it or copy details of the previous cell.
I tried locking the cell but my VBA code won't work if it's locked.


Code below:

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("O4:O100") ' 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 = "P" ' 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") & " " & Now()

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
 
Upvote 0
Hello, is there a way to lock a particular column with an existing VBA code?
Here's my code below. This adds the value on Column P when Column O says either Approved or Rejected.
Now, I would like to protect column P and make sure no one copies the values.
I want Column P to be protected and make sure no other user adds details in it or copy details of the previous cell.
I tried locking the cell but my VBA code won't work if it's locked.


Code below:

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("O4:O100") ' 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 = "P" ' 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") & " " & Now()

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
1667999138947.png
 
Upvote 0
I provided a very simple code in post #6 that should do what you want.
So why you still want to try and use your code in Post 24 is beyond me.
To lock a column and then try and run a script to change data on that column probable will not work. I have never tried that.

I would think you would have to add a line or two to the script to unlock the column then the code runs and then add more code at the end to again lock the column.
But since you still want to run your very long code which I do not understand I do not want to try and add in code to your code to lock and unlock the column
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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