Group/ungroup on a protected sheet

Hello

Ability to group and ungroup rows and columns on a protected worksheet in Excel

Private Sub Workbook_Open() With Worksheets("Sheet1") .EnableAutoFilter = True .EnableOutlining = True .Protect Contents:=True, Password:="Toto", UserInterfaceOnly:=True End With End Sub

EnableAutoFilter
UserInterfaceOnly
EnableOutlining

Thank you

--
Archivist at work :))

To make it simple:

1/ Make the "Developer" tab visible
-> Go to "File" -> "Options" -> "Customize Ribbon" and check the "Developer" tab
2/ Go to the "Developer" tab
3/ Click on "Visual Basic"
4/ Double-click on "ThisWorkbook" in the tree on the left
5/ Paste this piece of code:

Private Sub Workbook_Open()
Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
With ActiveWorkbook.Worksheets(I)
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, UserInterfaceOnly:=True
End With
Next I

End Sub

6/ Save (the format may be different as macro support is required)

When you protect the sheet, you should have several checkboxes (Contents, cells, etc.)

I don’t have XP at the moment, remind me what these options are and I’ll tell you which one to uncheck…
--
Why complicate life by keeping it simple when it's so easy to make it complicated?

Hello,

I don't understand, I've tried all your codes and it's still not working!!
My goal is: to protect a sheet while leaving certain cells editable (so far so good), and allowing the user to open and close the +/- (outline).

It seems that the codes you provided in this exchange match my request, but when I do it, it doesn't work!

Well, since I’m a clueless in VBA, I will describe what I'm doing:

I right-click on the sheet in question, click on "view code," and in the window that appears, I put:

Private Sub Workbook_Open()
With Worksheets("SAISIE GESTION")
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, Password:="", UserInterfaceOnly:=True
End With
End Sub

I save my Excel document, close it, reopen it, and when I click on the +, it tells me that I can't do it because my sheet is protected!

If someone could enlighten me, that would be great!

Thanks in advance and long live forums!

Thank you for your responses ...

@ LatelyGeek: I started by protecting the sheet by checking all the options (minimum security), and we can then add columns, hide or show rows, but the whole grouping/ungrouping feature is unavailable ...

@ Raymond: Indeed, it looks a bit like that, but I don't even have the possibility to give it a nudge :)
Why does Excel differentiate so much between the ability to hide/show and that of grouping/ungrouping :s

Have a nice day

Well, too bad, it was a lead...

--
Why make life complicated by keeping it simple when it’s so easy to make it complicated?

Thank you very much, it's perfect!!!

Happy for you that a helpful Anonymous has proposed a macro solution. I don't know anything about VBA, and I can't even test the effectiveness and ease of use of this method. I hope it will be seamless for the recipient of your file. Cheers.
--
Retirement is great! Especially in the Caribbean... :-)
Raymond

In fact, before testing it, I tried to understand it (to avoid any unpleasant surprises...)
From a purely functional point of view, it's not bad; it actually protects the sheet (even if it wasn't protected) with the ability to modify the plan...
However, it requires putting the password in full in the macro... which is inherently a security flaw. On the other hand, for anyone who knows Access, the security of an Excel password is really just for novices :)

Nonetheless, I don't know if this has evolved in the new versions of Excel, but having to go through a macro to achieve this surprises me a bit :s

Thank you all for your help.

where is it? before? after? isn't it already marked there?

Private Sub Workbook_Open()
With Worksheets("SAISIE GESTION")
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, Password:="", UserInterfaceOnly:=True
End With
End Sub

sorry, I really don't know anything about it...

Hehe! Well no, it's still not working.

There must be something simple, but I feel like I've tried everything.

Thanks anyway.

If anyone else has an idea...

Hello,

I am a novice in VBA and I would like to benefit from my grouping while having protected sheets.

I can't achieve this action; I already have a macro that protects my sheets all at once, is that the problem?

Here is what I wrote to obtain the grouping/un-grouping action, but it doesn't work and gives an error on this line (in bold).
"error 9"

Private Sub Workbook_Open()
With Worksheets("Sheet7")
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, Password:="Toto", UserInterfaceOnly:=True
End With
End Sub

The program never asks me for the password; is that normal?
What should I add or remove?

Thank you very much for your help
See you soon.

Hello,

facing the same issue, I'm looking into the subject.

The piece of code mentioned in previous messages works as long as the file is open.

But after closing and reopening the file, the protection remains, but the restrictions we were trying to remove on collapsing/expanding are not kept.

That's why it needs to be placed in a function that runs when opening the Excel file (Private Sub Workbook_Open()) and not in a regular sub like I'm currently doing.

However, since the computers have a higher security level in our organization, running this macro on opening is not possible.

So if anyone has a workaround to make the restrictions persist for the next opening of the file, I would appreciate it.

Have a great day.
Best regards.

Good evening,

I too have the same issue; I have several sheets with grouped columns or rows. I place them one after the other like in the example above, but it doesn't work because it highlights the worksheets ("sheet name") in yellow.
Can you help me find where the error is coming from?

Thank you in advance.

Best regards.

Good evening Grangcycy,

Instead of "sheet name", did you write the name of your sheet... for example, if the sheet you want to lock is called "bidule", you would write With Worksheets("bidule").

I also have a question to include in this post, which is quite old from 2008! For my part, the locking macro works very well (I assigned it to a custom button in my toolbar). I was wondering if I could have a similar macro that would allow me to unlock it by clicking on a personal button, instead of going into tools, protection...

Thank you in advance and Grangcycy I hope it will work for you.

```vb
Sub MaMacro()
ActiveSheet.Unprotect
End Sub
```

"How to open a locked door anyway?"

That's exactly the question you are asking!
Hiding lines to conceal certain information is one of the primary goals of sheet protection.
So you have to choose between using Group-ungroup and Protecting the sheet; it's one OR the other.
--
Retirement is great! Especially in the West Indies... :-)
Raymond