UDF and Enable Content Issue

[szymagic]

Hello,

I have a UDF that is supposed to track changes for a defined range of cells. So if anything within that range changes record who made the change and when.

The problem I am running into is that whenever a new user opens the file it asks them to Enable Content which, when they do Enable Content, sends a "push" through the sheet and every UDF is refreshed and overwrites all of the previous tracked info.

Public Function TRACKING(Variable_Range) As String


    If Not Application.Intersect(Variable_Range, Variable_Range) Is Nothing Then
    
        TRACKING = Environ$("UserName") & ", " & Date & ", " & Time
    
    End If
    
End Function

I tried having an Worksheet open event set calculation to manual, which works for the enable content button, but when you set the calculation to automatic the "push" happens again.

I would much rather use an elegant UDF than a hefty worksheet change macro. Any help would be appreciated! I'm in a 32-bit 2016.

Thank you!!

 

[mole999]

if you can use lastrow then your tracking sheet should place it as the last entry thus avoiding overwrites

 

[szymagic]

Thanks mole999, I'm not sure if we are on the same page. I am trying to use it as a UDF for one Target cell. So the layout would look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UDF[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]UDF[/TD]
[TD]t[/TD]
[TD]k[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]UDF[/TD]
[TD]u[/TD]
[TD]o[/TD]
[TD]p[/TD]
[/TR]
</tbody>[/TABLE]



And if you change any of the lower case letters in row 1, the UDF in A1 would automatically updated to "mole999, 5/24/2019, 08:05:23 AM"
And then if I were to make a change tomorrow in the same row, the UDF would update itself to "szymagic, 5/25/2019, 08:07:45 AM"

That's what is supposed to happen, continuously overwriting to the last person to make a change in the corresponding row.


What is happening is if someone new opens the file and needs to "Enable Content" excel for some reason pushes through a sheet calculation that updates every UDF in the workbook to that new user's name, date and time (even if they didn't make any changes). So it would look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]new user, 5/26/2019, 08:34:41 AM[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]new user, 5/26/2019, 08:34:41 AM[/TD]
[TD]t[/TD]
[TD]k[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]new user, 5/26/2019, 08:34:41 AM[/TD]
[TD]u[/TD]
[TD]o[/TD]
[TD]p[/TD]
[/TR]
</tbody>[/TABLE]


It's best if you just throw that code into your own file and check it out.

It works like a charm if you do not have to "Enable Content" when you first open it, it only breaks when a new user has to "Enable Content"

I hope I explained myself well enough!

Thanks!

 

[Joe4]

A UDF is probably not the way to go here, as it will be re-calculated every time a calculation is done. You want a hard-coded value, if you do not want the value to change.
What you really want is a date/time stamp that happens when a value in a pre-defined range is updated.
You would typically do that with a Worksheet_Change event procedure, which is VBA code that automatically runs whenever specific data is manually updated.
If you search on "DateTime Stamp VBA code", you should find thousands of examples of this.

Let us know if you need help putting it together (we would need to know the details, such as what are the affected ranges, and what columns should this value go in).

 

[szymagic]

Yea that's unfortunate. I gotcha, wrote something yesterday to keep a running list of changes. Not quite as elegant as the UDF but it could get the job done.

I just find it weird that the UDF doesn't update when you calculate the sheet. It might be my lack of understanding with Application.Intersect, but it is just strange to me.

Thanks for the help

 

[Joe4]

wrote something yesterday to keep a running list of changes. Not quite as elegant as the UDF but it could get the job done.

Are you using a Worksheet_Change event procedure? That is the most direct and efficient way.
If you post your here, we can take a look at it and see if we can help you clean/fix it up, and make it more concise and efficient.

 

[szymagic]

Sure, it works alright, and I haven't noticed any lag. It hasn't been applied to an actual spreadsheet yet, but I can't imagine it would goof up.

Private Sub Worksheet_Change(ByVal Target As Range)    Dim KeyCells As Range, lastrow As Variant
    Set KeyCells = Range("A1:C10")
    Application.ScreenUpdating = False
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        
        Sheets("Sheet3").Activate
        lastrow = Sheets("Sheet3").Range("A1").End(xlDown).Row + 1
        Sheets("Sheet3").Cells(lastrow, 1).Value = Environ$("UserName")
        Sheets("Sheet3").Cells(lastrow, 2).Value = Date
        Sheets("Sheet3").Cells(lastrow, 3).Value = Time
        Sheets("Sheet3").Cells(lastrow, 4).Value = Target.Address
        Sheets("Sheet3").Cells(lastrow, 5).Value = Target.Value
        Sheets("Sheet2").Activate
        
       
    End If
    Application.ScreenUpdating = False
End Sub

 

[Joe4]

Which sheet module is this code placed in (Sheet2 or Sheet3)?
Is Sheet2 the data sheet, and Sheet3 the log sheet and you are just logging the values into the next available row on Sheet 3?

You should be able to do this without having to activate any sheets, like this:
And note that Range(Target.Address) is really just the same thing as Target.
Also, you forgot to turn ScreenUpdating back on (still set to False at the end).

One thing that you haven't taken into account is what if a user updates multiple cells at once (i.e. through a Copy command).
In that case, I assume that you want a logged entry for each updated cell, right?

If all my asumptions are correct, here is what the code should look like:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    Dim cell As Range
    Dim lastrow As Long
    
    Application.ScreenUpdating = False
    
    Set KeyCells = Intersect(Range("A1:C10"), Target)
    If Not KeyCells Is Nothing Then
        For Each cell In KeyCells
            lastrow = Sheets("Sheet3").Range("A1").End(xlDown).Row + 1
            Sheets("Sheet3").Cells(lastrow, 1).Value = Environ$("UserName")
            Sheets("Sheet3").Cells(lastrow, 2).Value = Date
            Sheets("Sheet3").Cells(lastrow, 3).Value = Time
            Sheets("Sheet3").Cells(lastrow, 4).Value = cell.Address
            Sheets("Sheet3").Cells(lastrow, 5).Value = cell.Value
        Next cell
    End If
    
    Application.ScreenUpdating = True

End Sub

 

[szymagic]

Thank you Joe!

 

[Joe4]

You are welcome.