Dec 16 2019 07:19 PM
I have a spreadsheet with a macro which is designed to update a date when the line that that date sits on is edited. The macro runs fine on my computer. When I send the spreadsheet to my client, she has to first enable editing and then enable the macro. This means that the macro runs and updates the dates in the whole sheet.
How can I disable or get around the requirement to enable both editing and macros only on that sheet so when my client opens it, the macro does not run. I have enabled macros in my excel, but that does not transfer across to when she opens the sheet. thank you in advance Lisa
Dec 16 2019 10:37 PM
@LisaKSS2018 If you open Excel and it detects your file comes from an untrusted location (like an email) Excel goes into protected mode. As soon as you change to edit mode, Excel may fully recalculate, there is nothing you can do to avoid that. Best is to remove your user defined function and formula and right-click the sheet tab and choose View code and use something like this:
Option Explicit
Dim NoEvent As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.ListObjects("Table2").ListColumns("Product").DataBodyRange.Resize(, 8)) Is Nothing Then Exit Sub
If NoEvent Then Exit Sub
NoEvent = True
Intersect(Target.EntireRow, Me.Range("Table2[Updated date]")).Value = Now
NoEvent = False
End Sub
Dec 16 2019 11:11 PM