Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Worksheet protection with grouped rows?
Hi,
I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
#2
|
|||
|
|||
Hi
AFAIK not possible without creating a macro which first unprotects the sheet, groups/ungroups and protects t´he sheet again -- Regards Frank Kabel Frankfurt, Germany "pmw5" schrieb im Newsbeitrag ... Hi, I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
#3
|
|||
|
|||
Frank,
Thanks for your answer. I can see logically how your solution would work, however writing such a macro is beyond my skills! Thanks anyway for the answer. (also what does AFAIK stand for?) Does anyone else have any suggestions? I am amazed XL doesn't allow you to check some option to allow the group/ungroup function to still work on protected sheets... "Frank Kabel" wrote: Hi AFAIK not possible without creating a macro which first unprotects the sheet, groups/ungroups and protects t´he sheet again -- Regards Frank Kabel Frankfurt, Germany "pmw5" schrieb im Newsbeitrag ... Hi, I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
#4
|
|||
|
|||
Hi Pmw5,
With the worksheet unprotected: Right-Click the Excel icon at the extreme left of the Menu Bar Select the View Code option Paste the following code: Private Sub Workbook_Open() With Worksheets("Sheet1") '<<===== CHANGE SHEET NAME .EnableOutlining = True .Protect Password:="PASSWORD", _ Contents:=True, UserInterfaceOnly:=True '<< CHANGE PASSWORD End With End Sub Change PASSWORD to your choice of password. Change Sheet1 to your sheet name. Click anywhere in the posted code and hit the F5 function key. Save the workbook. --- Regards, Norman "pmw5" wrote in message ... Frank, Thanks for your answer. I can see logically how your solution would work, however writing such a macro is beyond my skills! Thanks anyway for the answer. (also what does AFAIK stand for?) Does anyone else have any suggestions? I am amazed XL doesn't allow you to check some option to allow the group/ungroup function to still work on protected sheets... "Frank Kabel" wrote: Hi AFAIK not possible without creating a macro which first unprotects the sheet, groups/ungroups and protects t´he sheet again -- Regards Frank Kabel Frankfurt, Germany "pmw5" schrieb im Newsbeitrag ... Hi, I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
#5
|
|||
|
|||
Hi Pmw5,
Missed one step: After hitting the F5 key, Press Alt-F11 to return to Excel --- Regards, Norman "Norman Jones" wrote in message ... Hi Pmw5, With the worksheet unprotected: Right-Click the Excel icon at the extreme left of the Menu Bar Select the View Code option Paste the following code: Private Sub Workbook_Open() With Worksheets("Sheet1") '<<===== CHANGE SHEET NAME .EnableOutlining = True .Protect Password:="PASSWORD", _ Contents:=True, UserInterfaceOnly:=True '<< CHANGE PASSWORD End With End Sub Change PASSWORD to your choice of password. Change Sheet1 to your sheet name. Click anywhere in the posted code and hit the F5 function key. Save the workbook. --- Regards, Norman "pmw5" wrote in message ... Frank, Thanks for your answer. I can see logically how your solution would work, however writing such a macro is beyond my skills! Thanks anyway for the answer. (also what does AFAIK stand for?) Does anyone else have any suggestions? I am amazed XL doesn't allow you to check some option to allow the group/ungroup function to still work on protected sheets... "Frank Kabel" wrote: Hi AFAIK not possible without creating a macro which first unprotects the sheet, groups/ungroups and protects t´he sheet again -- Regards Frank Kabel Frankfurt, Germany "pmw5" schrieb im Newsbeitrag ... Hi, I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
#6
|
|||
|
|||
Many thanks Norman! Much appreciated.
"Norman Jones" wrote: Hi Pmw5, Missed one step: After hitting the F5 key, Press Alt-F11 to return to Excel --- Regards, Norman "Norman Jones" wrote in message ... Hi Pmw5, With the worksheet unprotected: Right-Click the Excel icon at the extreme left of the Menu Bar Select the View Code option Paste the following code: Private Sub Workbook_Open() With Worksheets("Sheet1") '<<===== CHANGE SHEET NAME .EnableOutlining = True .Protect Password:="PASSWORD", _ Contents:=True, UserInterfaceOnly:=True '<< CHANGE PASSWORD End With End Sub Change PASSWORD to your choice of password. Change Sheet1 to your sheet name. Click anywhere in the posted code and hit the F5 function key. Save the workbook. --- Regards, Norman "pmw5" wrote in message ... Frank, Thanks for your answer. I can see logically how your solution would work, however writing such a macro is beyond my skills! Thanks anyway for the answer. (also what does AFAIK stand for?) Does anyone else have any suggestions? I am amazed XL doesn't allow you to check some option to allow the group/ungroup function to still work on protected sheets... "Frank Kabel" wrote: Hi AFAIK not possible without creating a macro which first unprotects the sheet, groups/ungroups and protects t´he sheet again -- Regards Frank Kabel Frankfurt, Germany "pmw5" schrieb im Newsbeitrag ... Hi, I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet protection with grouped rows?
Does anyone know how to do this with multiple worksheets? I appear to be
able to do with a single sheet - but it does not recognise the code on a second one. Thanks "pmw5" wrote: Many thanks Norman! Much appreciated. "Norman Jones" wrote: Hi Pmw5, Missed one step: After hitting the F5 key, Press Alt-F11 to return to Excel --- Regards, Norman "Norman Jones" wrote in message ... Hi Pmw5, With the worksheet unprotected: Right-Click the Excel icon at the extreme left of the Menu Bar Select the View Code option Paste the following code: Private Sub Workbook_Open() With Worksheets("Sheet1") '<<===== CHANGE SHEET NAME .EnableOutlining = True .Protect Password:="PASSWORD", _ Contents:=True, UserInterfaceOnly:=True '<< CHANGE PASSWORD End With End Sub Change PASSWORD to your choice of password. Change Sheet1 to your sheet name. Click anywhere in the posted code and hit the F5 function key. Save the workbook. --- Regards, Norman "pmw5" wrote in message ... Frank, Thanks for your answer. I can see logically how your solution would work, however writing such a macro is beyond my skills! Thanks anyway for the answer. (also what does AFAIK stand for?) Does anyone else have any suggestions? I am amazed XL doesn't allow you to check some option to allow the group/ungroup function to still work on protected sheets... "Frank Kabel" wrote: Hi AFAIK not possible without creating a macro which first unprotects the sheet, groups/ungroups and protects t´he sheet again -- Regards Frank Kabel Frankfurt, Germany "pmw5" schrieb im Newsbeitrag ... Hi, I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet protection with grouped rows?
Private Sub Workbook_Open()
Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets With ws .EnableOutlining = True .Protect Password:="PASSWORD", _ Contents:=True, UserInterfaceOnly:=True End With Next ws End Sub Gord Dibben MS Excel MVP On Tue, 17 Jul 2007 05:06:01 -0700, E Halliday wrote: Does anyone know how to do this with multiple worksheets? I appear to be able to do with a single sheet - but it does not recognise the code on a second one. Thanks "pmw5" wrote: Many thanks Norman! Much appreciated. "Norman Jones" wrote: Hi Pmw5, Missed one step: After hitting the F5 key, Press Alt-F11 to return to Excel --- Regards, Norman "Norman Jones" wrote in message ... Hi Pmw5, With the worksheet unprotected: Right-Click the Excel icon at the extreme left of the Menu Bar Select the View Code option Paste the following code: Private Sub Workbook_Open() With Worksheets("Sheet1") '<<===== CHANGE SHEET NAME .EnableOutlining = True .Protect Password:="PASSWORD", _ Contents:=True, UserInterfaceOnly:=True '<< CHANGE PASSWORD End With End Sub Change PASSWORD to your choice of password. Change Sheet1 to your sheet name. Click anywhere in the posted code and hit the F5 function key. Save the workbook. --- Regards, Norman "pmw5" wrote in message ... Frank, Thanks for your answer. I can see logically how your solution would work, however writing such a macro is beyond my skills! Thanks anyway for the answer. (also what does AFAIK stand for?) Does anyone else have any suggestions? I am amazed XL doesn't allow you to check some option to allow the group/ungroup function to still work on protected sheets... "Frank Kabel" wrote: Hi AFAIK not possible without creating a macro which first unprotects the sheet, groups/ungroups and protects t´he sheet again -- Regards Frank Kabel Frankfurt, Germany "pmw5" schrieb im Newsbeitrag ... Hi, I have a report which has some grouped rows. The normal view of the report shows these rows grouped, and therefore only shows the total of the hidden/grouped rows. I want to protect certain cells in the report so they cannot be modified, however, when I protect the sheet, I can no longer ungroup the grouped rows. I could of course ungroup all the grouped rows and then protect the sheet, however the report would be much to big then. Does anyone know how to protect a worksheet yet still be able to use the group/ungroup rows buttons? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use outline data (grouped rows) in a protected worksheet? | Excel Discussion (Misc queries) | |||
How to use outline data (grouped rows) in a protected worksheet? | Excel Discussion (Misc queries) | |||
copyright and worksheet protection | Excel Discussion (Misc queries) | |||
Seeking help for total worksheet protection | Excel Discussion (Misc queries) | |||
Display selected rows from one worksheet to another | Excel Worksheet Functions |