Forum Discussion
.XLSM workbook not showing links in column
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!
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
Patrick2788 Hmm, I'm afraid I looked cross-eyed. Of course the Navigation button is there on my build.
Patrick2788 That navigation pane isn't available for everyone. It's not on my Excel (M365, monthly channel)!
- 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?
