Community Forum Community Forum

Group and Ungroup - non-Modano users

GroupingProtection
12 posts / 0 new
Log in or register to post comments.
Last post
4 years ago
#1
Tarjei Kirkesaether A+ 40
TK
Group and Ungroup - non-Modano users

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?

4 years ago
Michael Hutchens A+ 129

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.

4 years ago
Tarjei Kirkesaether A+ 40
TK

Thanks Michael!

6 months ago
Daniel Quill A+ 1
DQ

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? 

5 months ago
Michael Hutchens A+ 129

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.

5 months ago
Tarjei Kirkesaether A+ 40
TK

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?

 

 

5 months ago
Jun Yan A+ 73

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!

4 months ago
Cheyne Buckley A+ 4
CB

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.

 

4 months ago
Jun Yan A+ 73

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

4 months ago
Tarjei Kirkesaether A+ 40
TK

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.

Sub ProtectAll()

Dim wSheet As Worksheet

'all sheets are protected with the below parameters

For Each wSheet In Worksheets

wSheet.Protect Password:="abcd", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

wSheet.EnableOutlining = True


Next wSheet

'The following sheets remain unprotected so users can manually adjust if required

       Worksheets("Project").Unprotect "abcd"
       Worksheets("BC1").Unprotect "abcd"
       Worksheets("BC2").Unprotect "abcd"
       Worksheets("BC3").Unprotect "abcd"

End Sub

 

4 months ago
Michael Hutchens A+ 129

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.

1 month ago
Tarjei Kirkesaether A+ 40
TK

Hi Michael,

Any news on when version 10.20 will be released? I'm very much looking forward to getting around this grouping issue and I'm happy to do some beta testing for you if it's still WIP:-)

Cheers

T

Get Started

Whether you'd like us to build you a model, streamline your organization's modeling, or become a better modeler, we're here to help.

Tell us about your organization and your needs and one of our experts will be in touch to get you started.

1. Your Needs

2. Your Details

Full Name *
Company Name *
Position *
Phone Number *
Referral Code
By submitting this form, you are providing us with consent to communicate with you for the purpose of responding to your request. Our outreach, either by email or telephone, will, where possible, leverage the data you have provided to us to ensure that our communications are tailored to your specific needs and interests.

3. Your Model Requirements

Add another file
NextBackCancel