How do I trust all files with macros that I've written?

[Johnny C]

This may sound bizarre. I've written a lot of macro's in my time. Some I'm not proud of but some I am (the one that copied ranges charts to Powerpoint, the one that identified external sources in a file) but maybe not the one that on a 10,000th entry opened the mousedraw and asked for cheese to be put in it, or another that one the 10,000th entry bounced a smiley face around the screen for 10 secs.). Anyway, a lot of files I've written have macros. Excel has started telling me macros are disabled because 'The source of this file [me] is unstrusted'.
I'm fairly sure I'm not schizophrenic, and oddly, perhaps weirdly in Microsoft eyes, I trust myself. Except with naga chilli sauce but that's a different problem.
I know I can digitally sign a macro (involving a middle finger), but it seems I have to go through every folder I've had a macro in, add it to the circle of Trust (Microshaft Trust centre, within FBI remit, stick your badges where the sun don't shine, I'm sure you're monitoring MrExcel) Then go and digitally sign (middle finger) every spreadsheet.

I know MS want to kill Excel, but is there a simple way I can end this misery.

Dear Billy Goats. I will be dead shortly. Please let me live out my dying days without having to put my balls through a wrencher.

 

[Joe4]

This has nothing to do with MS wanting to kill Excel. VBA and code is a serious security concern, so there is a valid reason for it.
You "could" change the security settings to allow all Excel VBA code, but that is also a security concern, as you are allowing anyone's VBA code to run (and malicious code would not be "checked").

A better solution may be to move all your VBA code workbooks to a new folders, and set up those folders as Trusted Locations. Then anything in those folders will be trusted, so you don't need to update every workbook.

 

[Johnny C]

I set my settings to trust all. It still doesn't trust any.

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

For Excel 365, here is how you would set those settings: Enable or disable macros in Microsoft 365 files - Microsoft Support

 

[elynoy]

Right click on file properties and unblock file

 

[Johnny C]

When I right click I don't get the 'Unblock' Option.

 

[MARK858]

I know MS want to kill Excel, but is there a simple way I can end this misery.

Is this correct?

 

[Johnny C]

Blimey.
That is going to do people's heads in if rolled out corporately. People who know vlookups and can do pivot tables and charts and have inherited macros. Or who use external software that use Add-ins. Fine for big corps with IT departments but small companies with no Excel expertise?
I see a lucrative job opportunity

 

[MARK858]

I see a lucrative job opportunity

Or just set up trusted locations as suggested in post 2 (you only normally get a warning in the first place if the files have been downloaded from the internet or have been emailed)

 

[Johnny C]

Hi Mark.
Lol '2003 or older' refers to me. I almost wish I was on XL2003 or older. Allowing people who have little knowledge of Excel to unwittingly create Split functions is at least a pain but also creating carnage. At least with CSR functions they couldn't enter them if they didn't know how.
And having to page down and page up to get values to refresh in small spreadsheets where Calculate times isn't a problem, what's all that about? I'd be sitting there with a page with some XLOOKUPs all returning zero and thinking how can that not possible work? open a new sheet and repeat and it works, go back to the old sheets and suddenly everything works. Plus getting the blackouts where the contents of cells you've changed just go black until PgUpPgDown