Excel privacy settings

%3CLINGO-SUB%20id%3D%22lingo-sub-1069457%22%20slang%3D%22en-US%22%3EExcel%20privacy%20settings%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1069457%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20a%20macro%20which%20is%20designed%20to%20update%20a%20date%20when%20the%20line%20that%20that%20date%20sits%20on%20is%20edited.%26nbsp%3B%20The%20macro%20runs%20fine%20on%20my%20computer.%26nbsp%3B%20When%20I%20send%20the%20spreadsheet%20to%20my%20client%2C%20she%20has%20to%20first%20enable%20editing%20and%20then%20enable%20the%20macro.%26nbsp%3B%20This%20means%20that%20the%20macro%20runs%20and%20updates%20the%20dates%20in%20the%20whole%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20disable%20or%20get%20around%20the%20requirement%20to%20enable%20both%20editing%20and%20macros%20only%20on%20that%20sheet%20so%20when%20my%20client%20opens%20it%2C%20the%20macro%20does%20not%20run.%26nbsp%3B%20I%20have%20enabled%20macros%20in%20my%20excel%2C%20but%20that%20does%20not%20transfer%20across%20to%20when%20she%20opens%20the%20sheet.%26nbsp%3B%20thank%20you%20in%20advance%20Lisa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1069457%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1069627%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20privacy%20settings%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1069627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F491054%22%20target%3D%22_blank%22%3E%40LisaKSS2018%3C%2FA%3E%20If%20you%20open%20Excel%20and%20it%20detects%20your%20file%20comes%20from%20an%20untrusted%20location%20(like%20an%20email)%20Excel%20goes%20into%20protected%20mode.%20As%20soon%20as%20you%20change%20to%20edit%20mode%2C%20Excel%20may%20fully%20recalculate%2C%20there%20is%20nothing%20you%20can%20do%20to%20avoid%20that.%20Best%20is%20to%20remove%20your%20user%20defined%20function%20and%20formula%20and%20right-click%20the%20sheet%20tab%20and%20choose%20View%20code%20and%20use%20something%20like%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EOption%20Explicit%0A%0ADim%20NoEvent%20As%20Boolean%0A%0APrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Intersect(Target%2C%20Me.ListObjects(%22Table2%22).ListColumns(%22Product%22).DataBodyRange.Resize(%2C%208))%20Is%20Nothing%20Then%20Exit%20Sub%0A%20%20%20%20If%20NoEvent%20Then%20Exit%20Sub%0A%20%20%20%20NoEvent%20%3D%20True%0A%20%20%20%20Intersect(Target.EntireRow%2C%20Me.Range(%22Table2%5BUpdated%20date%5D%22)).Value%20%3D%20Now%0A%20%20%20%20NoEvent%20%3D%20False%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1069652%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20privacy%20settings%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1069652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20very%20much%20for%20your%20response.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eregards%3C%2FP%3E%3CP%3ELisa%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

2 Replies

@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

@Jan Karel Pieterse 

 

thank you very much for your response.  

 

regards

Lisa