UDF and Enable Content Issue

szymagic

Board Regular
Joined
Jul 8, 2016
Messages
61
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.

Code:
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!!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
if you can use lastrow then your tracking sheet should place it as the last entry thus avoiding overwrites
 

szymagic

Board Regular
Joined
Jul 8, 2016
Messages
61
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:

ABCD
1UDFxyz
2UDFtkq
3UDFuop

<tbody>
</tbody>



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:

ABCD
1new user, 5/26/2019, 08:34:41 AMxyz
2new user, 5/26/2019, 08:34:41 AMtkq
3new user, 5/26/2019, 08:34:41 AMuop

<tbody>
</tbody>


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!
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,894
Office Version
  1. 365
Platform
  1. Windows
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).
 
Last edited:

szymagic

Board Regular
Joined
Jul 8, 2016
Messages
61

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,894
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 8, 2016
Messages
61

ADVERTISEMENT

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.

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,894
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,894
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,263
Messages
5,769,111
Members
425,518
Latest member
seothaeng

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
Top