Is there any workaround to the problem I'm facing when creating models for others in that they can group and ungroup unless I release the workbook before I send it to them for testing or is this part of the Excel 'protected workbook' setup?
Is there any workaround to the problem I'm facing when creating models for others in that they can group and ungroup unless I release the workbook before I send it to them for testing or is this part of the Excel 'protected workbook' setup?
Stay up-to-date with Modano developments including new example models, content and learning exercises.
This is a painful nuance with Microsoft Excel - i.e. that sheets can be protected while allowing users to continue collapsing and expanding grouped rows and columns, but once the workbook is saved, closed and re-opened, grouped rows and columns cannot be collapsed or expanded.
This issue means that when you provide modular workbooks created using Modano (which protects the sheets to ensure that the modules remain in intact) to Excel users without Modano installed, they will be unable to expand or collapse grouped rows or columns.
We have contacted Microsoft about this before over the years, as we can't see how it's not a bug, but they've never done anything to change this behaviour.
The workaround is to ask 3rd parties to create a Modano account and install the free Community Edition of Modano, as this will allow them to collapse and expand grouped rows within modular workbooks of any size.
Thanks Michael!
Hi Michael, i note this was posted a while ago now. Has a solution been found for this issue that does not require the viewer to install the community edition of Modano?
Hi Daniel,
Apologies for the belated reply. Happily, I can confirm that we have found a solution to this issue and from version 10.20 of Modano - which we're aiming to release at the start of November 2020 - non-Modano users will be able to collapse and expand grouped rows in modular workbooks without them needing to be released/unmanaged.
If you want to ensure that you get this upgrade as soon as it's available please ensure that your update settings in Modano are set to Always.
It will be great to not have this issue going forward! M.
This is great news Michael, it will make sharing Modano workbooks across the organisation much easier!
Now that you got this working is it possible to get the spell checker working too, that would be very handy?
Also, my by far biggest complaints from (non-Modano) model users to date when sending them Modano files is that that they can't trace formulas using the built in trace precedents/dependents because the workbook is locked on their end. With that in mind, have you considered selling the old BPM Traverse as a standalone add-in? I'm currently using Ariexcel for auditing but I know a lot of users prefers Traverse and would probably pay a small monthly fee for it (Ariexcel is USD2 per month per user). I assume BPM Traverse gets around the limitation of tracing formulas in protected workbooks?
This is awesome news! We've been doing a workaround where we've built in Macros to allow for collapsing and expanded of semi-unmanaged version of the model.
Looking forward to this!
Has this been released?
I have a model which I need to provide to a big-4 bank (can't get them to install software) and would like them to have capability to expand rows.
Hi Cheyne,
If it's urgent, a workaround that I use is to release the workbook. Lock the structure of the workbook along with all the sheets.
I then set a password on the workbook and then use some VBA to allow for grouping / ungrouping on a sheet level, by popping a button in the sheets that I want to allow the grouping / ungrouping. Keep this minimal, you don't want to have to do this for every sheet... and really the bankers should only want to see stuff like annual or monthly financial statements.
You can use something as simple as:
ws.Protect Password:=myPW, userinterfaceonly:=True
ws.EnableOutlining = True
I'm sure there are security flaws doing this, but it's better than nothing for me. Make sure you lock away the project properties when doing this.
Hope that helps!
Jun
Hi Cheyne,
I've used a near identical approach to Jun with the below macro (which I can take no credit for, i's a copy paste from some forum). The macro protects all sheets (rather than do one at a time) in the workbook in one go and sets the password to abcd while still allowing grouping/ungrouping. You can also choose to leave some sheets unprotected.
Hi Guys,
From version 10.20 of Modano (due for release in early January 2021) you will not need to release workbooks for non-Modano users to be able to collapse and expand rows. So please be patient.
We're aware this is a frustration, and it's bad for us too as we'd rather our community don't release workbooks, so it's going to be a great thing not having to worry about this anymore.
M.