Using Group/Ungroup in protected worksheets

[Linus_99]

Hello,

I'm trying to protect a worksheet so that cells are protected but users can still group & ungroup sub-totals by using the Group/Ungroup symbols that appear at the margins.

When using the Protect command, I can't see a way to allow this to happen.

Does anyone know how this can be done for Excel 2003/2007 ?

Thanks

 

[tbiven78]

Give this a shot. This will hide and unhide groupings and is 2 seperate Macros. The users are more apt to use a Hide and Unhide button so this allows them to click one and get the desired resuts.

Sub UnGroup()
'
' UnGroup Macro
'
'
ActiveSheet.Unprotect Password:="password"
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Protect Password:="password"

End Sub

Sub Group()
'
' Group Macro
'
'
ActiveSheet.Unprotect Password:="password"
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Protect Password:="password"

End Sub

 

[beejey]

is there no non-VBA /macro solution available?

thanks

 

[tbiven78]

If there is I haven't found it yet.

 

[beejey]

ok, thx. i guess that's a no then.

 

[vanray]

This is exactly what I need, except I need to do it for multiple sheets instead of just one. I tried separating the sheet names by commas (see below), but that didn't appear to be the correct syntax. I'm guessing this is a quick fix, but I'm not sure what the code looks like. PLEASE HELP!

What I tried: With Worksheets("sales", "marketing", "research")
DIDN'T WORK

You would have to use a Workbook_Open macro to set the protection a special way when you open the workbook. The following needs to go in the ThisWorkbook module. The password is password - you can change the text between the quotation marks to what you need it to be. The sheet with the subtotaling is called sales - you can change that below.

Private Sub Workbook_Open()
With Worksheets("sales")
       .Protect Password:="password", Userinterfaceonly:=True
       .EnableOutlining = True
End With
End Sub

The 3rd item at the following site will show you how to put the code in the ThisWorkbook module.
http://www.contextures.com/xlvba01.html

 

[vanray]

Figured it out. You have to create a unique "WITH" statement for each sheet name within one sub. Whew!

 

[Antonio12]

Hello everybody. I'm as new to this forum as I am to excel VBA/Macros and I really need help from a pro.
I use Excel 2007 and run Windows XP.

The file has several sheets and works a lot with hyperlinks. The file needs to be protected because I cant have people making changes to it.

I have 2 problems that I would very much appreciate if someone could help me with.

I need for the file to open in the same sheet every time someone opens it regardless of if when they closed it they saved in another sheet. I created a sheet called HOME and I need the file to always open to that sheet. I tried the following but it doesn't work.

Private Sub Workbook_Open()
Worksheets("HOME").Activate
End Sub

I don't know where I have to place this code in the VBA project. Module/The Sheet/ThisWorkbook. Can anybody tell me why this is not working please.

The other problem is that since the file is protected, I can't group and Ungroup. I tried the following code:

Private Sub Workbook()
With Sheet2
.Protect Password:="Antonio123", UserInterfaceOnly:=True
.EnableOutlining = True
.EnableAutoFilter = True
.EnableCalculation = True
.EnableFormatConditionsCalculation = True
.EnableOutlining = True
.EnableSelection = xlLockedCells
End With
End Sub

Again, I don't know if I have to paste this in Module/The Sheet/ThisWorkbook.

The interesting thing is that this code sort of works. When I open the file it doesn't work even if I press enable macros, but if after I go to the VBA and on the sheet where the code is I press the play Icon on the toolbar above the code, it works.


Can one of you Excel geniuses help me out please!!!

Thank you and sorry for the long text, but don't know how else to explain this.

 

[Jaspal]

Hello Tracy,

I have a worksheet that is protected. I have grouped protected columns A-C with the grand total in Column D visible. I want the users to be able to group and ungroup the coulumns A-C with clicking the "+" on the excel sheet.

I have added another macro for end users which adds a row and only unprotects Cells in column A-C in the newly added row.

I tried the the below macro in the "workbook_open" event, but that does not work?

Thank you in advance.


Private Sub Workbook_Open()
With Worksheets("Detailed")
.Protect Password:="protect", Userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

 

[robrob]

You would have to use a Workbook_Open macro to set the protection a special way when you open the workbook. The following needs to go in the ThisWorkbook module. The password is password - you can change the text between the quotation marks to what you need it to be. The sheet with the subtotaling is called sales - you can change that below.

Private Sub Workbook_Open()
With Worksheets("sales")
       .Protect Password:="password", Userinterfaceonly:=True
       .EnableOutlining = True
End With
End Sub

The 3rd item at the following site will show you how to put the code in the ThisWorkbook module.
http://www.contextures.com/xlvba01.html

______________________________________________
Hello Mr. Tracy,

Thanks for the code. I am new to visual basics. I need help with a situation; I would like to allow access to change data in a section/range of cells in the sheet, but protect the rest of the sheet and still allow grouping/and ungrouping; is there additional code needed for this, is it possible? Actually I the document has over 200 tabs, but this will only to apply about half of them, which have the same format.




Thanks for your help.

 

[robrob]

Hello, Sir:

Im new to Visual Basics and Macros, but am working it out, just started working with it today.

The code works great for me. I was able to unlock the cells which i needed enable for data input. Although I am having an issue when i close the document and open it again. It feels like the Macro did not save properly, but when i "view code" by right click directly to the tab, i can see the code, but it appears to be inactive, until i click "run" again.
How can i keep the Macro running, or operational until i unprotect the sheet with password.

Thank you very much for your time!
</SPAN>