Expand/Collapse disabled

[Former Member]

Hi experts,

I would like to ask on how can i enable the expand/collapse button if the report templates are already locked?

i tried to unlock the ID cell which i need to expand or collapse, but when i put the protection to my workbook, the button for expand/collapse still become disable.

i also tried to check all the checkbox in users option but still disabled.

Thanks and Best Regards,

[gajendra_moond]

Hi Marlon

This is one of the ways that you can leverage on a protected sheet but it involves a little VBA.

In VBA editor (Alt+F11), double click the sheet where you wish to expand a member and place this code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Value = The member you wish to expand Then

    ActiveSheet.Unprotect Password:="Your Password"

End If

End Sub

There is one problem though that the sheet stays unprotected. I am working to see how to enable protection again. Kindly let me know if this would work for you.

I am also trying ExpandMember API as an alternative but not getting any success.

[gajendra_moond]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Me.Protect Password:="Your Password", UserInterFaceOnly:=True

End Sub

This will retain your protection as well. Also, if your expansion includes "Member and Children", you will be able to collapse as well by double-clicking the original member.

Hope this helps.

[gajendra_moond]

Hi Marlon

Did the above suggestion work for you?

[Former Member]

Hi Marlon,

Do a full expansion of the data, use grouping to act as expand / collapse.

when you protect the sheet make sure you allow them to use the grouping function. so the rest of the sheet is protected.

Andy