Forum Discussion
.XLSM workbook not showing links in column
What you're encountering is a stricter macro security policy that's been going on for several months. Users will encounter one two things typically:
1) XLSM opens with no prompt to enable macros. Any macro buttons the workbook may have are de-activated and macros cannot be run via developer | macros.
2) XLSM opens with a red banner below the ribbon saying macros have been disabled because the source of the macros cannot be trusted.
The solutions typically involve 1 or more of the following:
1. Trust Center - Enable all macros
2. Trust Center | Trusted Documents - Clear trusted document cache
3. File | Info | Inspect document - clear document properties and personal information.
4. Add the location of the workbook as a trusted location
5. 'Unblock' the file through Windows properties:
Have you considered using the Navigation pane to quickly access sheets?
Thank you for this insight.
If I add the location of the workbook as a trusted location, is that user specific or would that inherit to anyone that opens the document?
I need to share this out to plant locations nationwide, having the recipients going to trust center as part of process isn't an option. I need them to be able to open the workbook and have it function as intended.
I have office 365, and it should be up to date, but don't have the navigation?
- Wouldn't it be easier to just keep a manual list of sheet names and use the HYPERLINK function to create links to them? I understand this depends on the use of the file and how often tabs are added and/or renamed. But the simplest of solutions is often best. Alternatively, you might write a script that updates the sheet list and call that script on a scheduled basis using Power automate. See for example this article (by me) which contains precisely that script:
https://jkp-ads.com/articles/excel-office-script-examples.asp#Adding_a_table_of_Contents_to_your_workbook
And how to call such a script using Power Automate (the example does so for many files, but it will give you an idea):
https://jkp-ads.com/articles/excel-office-script-call-from-power-automate.asp- Perhaps HyperLink function came about after the tutorial video I followed. As it is now, if I add a tab, it auto enters itself on the main page with the process followed in the video. I will give your link a view and see if that may work. Ultimately it is what works best for the users that I share the workbook to that have to update it. IT just needs to work without any manipulation on user end. Thanks much!
- If I save-as to the ancient Excel 97-2003 file format, the HYPERLINK function is retained. That indicates it has been around for a while 🙂
- Trusted locations are user-specific unless your admin adds the location via group policy (If that's possible. Don't quote me on that as I'm not an admin). MS claims the navigation pane is available in Excel 365 so it may come down to having M365 fully updated
https://support.microsoft.com/en-us/office/use-the-navigation-pane-in-excel-ddd037e7-22e3-41f0-8bbd-07f5479e92bf#:~:text=Open%20the%20Navigation%20pane.%20In%20an%20open%20workbook%2C,element%20name%20to%20open%20a%20menu%20of%20options.
