Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
pmw5
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
pmw5
 
Posts: n/a
Default

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   Report Post  
Norman Jones
 
Posts: n/a
Default

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   Report Post  
Norman Jones
 
Posts: n/a
Default

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   Report Post  
pmw5
 
Posts: n/a
Default

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use outline data (grouped rows) in a protected worksheet? biometris Excel Discussion (Misc queries) 1 January 17th 05 11:51 PM
How to use outline data (grouped rows) in a protected worksheet? biometris Excel Discussion (Misc queries) 0 January 17th 05 10:47 AM
copyright and worksheet protection dow Excel Discussion (Misc queries) 2 January 3rd 05 04:07 PM
Seeking help for total worksheet protection Joseph Geretz Excel Discussion (Misc queries) 10 December 31st 04 07:48 PM
Display selected rows from one worksheet to another Brian Excel Worksheet Functions 5 October 29th 04 12:26 PM


All times are GMT +1. The time now is 11:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"