.XLSM workbook not showing links in column

[JimTischler]

I have a .XLSM folder that I created.  There is no VBA, but the way some of the formulas are written it necessitates the need of .XLSM format.

ref: https://www.youtube.com/watch?v=ZZ9j8m1lT6w&t=18s

In a nutshell column A has links to all the tabs so that someone can just click on the link and go straight to their tab in the workbook. The cells are protected, but clickable to use the links.

I have this saved on a sharepoint site and have given all users access to the workbook.

I have ensured Macro Securities are set to 'with notification', I have also tried enable all Macros. Ensured recipients are editing in desktop version and not online

I have shared this document by link so that everyone can update their respective tab, but no one can see column A links.  They can see the formulas in the formula bar thats it.

The only solution is for them to each go to their trust center/Macro securities and enable all Macros and then reopen the workbook.  This is not an acceptable solution for the audience being asked to make the updates.  Additionally, while the Macro Sucurity setting 'With Notification' is showing as seleted, the users are not being prompted with the 'Enable Macros' button in the yellow banner at top of workbook.

Am I missing something?  How can I get the links in Column A to be visible for them.  This works for me just fine.

HELP!

[#2]

Two questions:

1. In trust center, macros, is "Enable Excel 4.0 Macros when macros are enabled" checked?
2. What are the trust center settings for trusted locations?

[#3]

@JimTischler 

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?

[#4]

@Patrick2788 That navigation pane isn't available for everyone. It's not on my Excel (M365, monthly channel)!

[#5]

@Patrick2788 Hmm, I'm afraid I looked cross-eyed. Of course the Navigation button is there on my build.

[#6]

@Patrick2788 

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?

[#7]

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-....

[#8]

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_wor...

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

[#9]

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!

[#10]

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 🙂