VBA Help

[Wisyr]

Hello experts,
I'm trying to complete as a practice excel spreadsheet that would help me track work progress, im having abit of trouble with VBA coding. Im looking for tips and advices on two things.

On this VBA to clean each cell when value is deleted .Or if cell is empty to clear date next to it ?

Private Sub worksheet_change(ByVal target As Range)
        If Not Intersect(Range("F2:F300,H2:H300,J2:J300,L2:L300,N2:N300,P2:P300,R2:R300"), target) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Intersect(Range("F2:F300,H2:H300,J2:J300,L2:L300,N2:N300,P2:P300,R2:R300"), target).Offset(0, 1).Value = Now
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If

End Sub

And the other question :
Is there any option to have a VBA that capture last user who modified a row ?

 

[Joe4]

Welcome to the Board!

Try the following code.
I am a little unclear if you want column U updated with the user if a value is removed, but the code can be easily amended either way.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if any cells updated in your watched range, and exit if not
    Set rng = Intersect(Range("F2:F300,H2:H300,J2:J300,L2:L300,N2:N300,P2:P300,R2:R300"), Target)
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells
    For Each cell In rng
        Application.ScreenUpdating = False
        Application.EnableEvents = False
'       If cell value is empty, clear value next to it
        If cell.Value = "" Then
            cell.Offset(0, 1).ClearContents
        Else
'       Otherwise add timestamp in cell next to it
            cell.Offset(0, 1).Value = Now()
        End If
'       Capture last updater in column U
        Cells(cell.Row, "U").Value = Environ("UserName")
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    Next cell

End Sub

 

[Wisyr]

It doesn't seem to work at all now. If you look at the picture you cant see status and next to it location. What I'm trying to do is when the status changes to input next to it date of change. If status is empty to clear the date. And i the U column as mentioned to capture last user who changed anything in the ROW. I will try to upload mini-sheet if this could help. I'm totally lost with coding on this one.

 

[Joe4]

My code should do EXACTLY that, eveything you have asked.
However, in order for it to run automatically, it MUST be placed in the proper SHEET module in VBA (the sheet you wish to apply it to).
If you place it in any other module (like one you manually create), it will not fire automatically.

Also, if you had an error, or stopped anywhere in the middle of the code, you may have disabled events. You need to turn them back on in order to run the code automatically.
You can turn them back on by running this little macro manually:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[rickert91]

I am designing a better inventory system for a University food service to track consumables within each department, and I have ran into the dreaded VBA wall. I am trying to move cells from one sheet to the next sheet based on value. But I just want certain cells in the other sheet. If the re-order quantity is greater than 0, I want columns B, C, D AND G to be copies automatically to the next sheet I cannot figure out how to get my workbook to you. I have done all the steps, but somehow, cannot get it to load. I would like to send it to you if you can provide me with a link or an email.

 

[Joe4]

I am designing a better inventory system for a University food service to track consumables within each department, and I have ran into the dreaded VBA wall. I am trying to move cells from one sheet to the next sheet based on value. But I just want certain cells in the other sheet. If the re-order quantity is greater than 0, I want columns B, C, D AND G to be copies automatically to the next sheet I cannot figure out how to get my workbook to you. I have done all the steps, but somehow, cannot get it to load. I would like to send it to you if you can provide me with a link or an email.

Since this is a new question, unrelated to the original question asked, please post your question to a New thread of your own.
That way it will also appears in the "Unanswered threads" listing that many people use to look for new unanswered questions.