Group/ungroup on a protected sheet
Solved/Closed
Tipunchie
-
FRS -
FRS -
Hello,
I am using Excel XP.
I have an Excel sheet with about 300 rows. I grouped the rows by category to make the file more readable...
So far, no problem, the +/- buttons and 1/2/3/4 work perfectly.
However, as soon as I protect the sheet, I can no longer use the +/- buttons and 1/2/3/4.
Why do I protect my sheet? Because I'm sending it to someone else and I don't particularly want them to mess it up, but I would still like them to benefit from easier reading through the +/- buttons and 1/2/3/4.
Thank you in advance
Olivier
I am using Excel XP.
I have an Excel sheet with about 300 rows. I grouped the rows by category to make the file more readable...
So far, no problem, the +/- buttons and 1/2/3/4 work perfectly.
However, as soon as I protect the sheet, I can no longer use the +/- buttons and 1/2/3/4.
Why do I protect my sheet? Because I'm sending it to someone else and I don't particularly want them to mess it up, but I would still like them to benefit from easier reading through the +/- buttons and 1/2/3/4.
Thank you in advance
Olivier
Configuration: Windows XP Internet Explorer 7.0
17 réponses
Hello
Ability to group and ungroup rows and columns on a protected worksheet in Excel
EnableAutoFilter
UserInterfaceOnly
EnableOutlining
Thank you
--
Archivist at work :))
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)
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?
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!
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
@ 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?
--
Why make life complicated by keeping it simple when it’s so easy to make it complicated?
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
--
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.
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...
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...
Me neither; I just re-read the post https://forums.commentcamarche.net/forum/affich-8003320-grouper-dissocier-sur-une-feuille-proteger#5
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...
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.
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.
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.
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.
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.
"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
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
I'm getting back to you a bit late on this issue, but I need to do exactly this operation on all the sheets in my workbook.
Any ideas to help me?
Thank you.
the formula works perfectly, just copy and paste it into the workbook and adjust according to the tab name and boom, it’s perfect, we can protect the sheet while allowing the option to open or close the groups.
thank you
For all the sheets in the workbook, you just need to do this for example:
Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, Password:="Toto", UserInterfaceOnly:=True
With Worksheets("Sheet2")
.EnableAutoFilter = True
.EnableOutlining = True
.Protect Contents:=True, Password:="Toto", UserInterfaceOnly:=True
End With
End With
End Sub
and there you go as many times as there are sheets
See you+