Group - Ungroup and Protect Sheet

I have a sheet that has rows and columns that are grouped.
I need to protect rollup portion of the grouped rows.
However, when I do that and protect the sheet, I can no longer group
and ungroup.
How can I allow the user to group and ungroup rows and columns but
protect the grouped row?
Example:
row 4 is a rollup of MY DATA with the next 10 rows as
supporting/detailed data.  I have created a group so that I can
contract 9 of the rows and show only the rollup MY DATA or expand it
and show all the subordinate data.  The formulas in the columns MY DATA
are protected while the cells in the supporting rows are available to
the user for change.

However, when I protect the sheet, the user can no longer group and
ungroup.  How can I fix this?

Glen

0
3/16/2006 10:53:56 PM
excel 39879 articles. 2 followers. Follow

2 Replies
475 Views

Similar Articles

[PageSpeed] 26

If you already have the outline/subtotals applied, you can protect the worksheet
in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
    With Worksheets("sheet1")
        .Protect Password:="hi", userinterfaceonly:=True
        .EnableOutlining = True
        .EnableAutoFilter = True
    End With
End Sub

It needs to be reset each time you open the workbook.  (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


"glen.e.mettler@lmco.com" wrote:
> 
> I have a sheet that has rows and columns that are grouped.
> I need to protect rollup portion of the grouped rows.
> However, when I do that and protect the sheet, I can no longer group
> and ungroup.
> How can I allow the user to group and ungroup rows and columns but
> protect the grouped row?
> Example:
> row 4 is a rollup of MY DATA with the next 10 rows as
> supporting/detailed data.  I have created a group so that I can
> contract 9 of the rows and show only the rollup MY DATA or expand it
> and show all the subordinate data.  The formulas in the columns MY DATA
> are protected while the cells in the supporting rows are available to
> the user for change.
> 
> However, when I protect the sheet, the user can no longer group and
> ungroup.  How can I fix this?
> 
> Glen

-- 

Dave Peterson
0
petersod (12004)
3/16/2006 11:46:14 PM
Works like a champ!  Thanks

Glen

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4419F8C6.2B05DA9A@verizonXSPAM.net...
> If you already have the outline/subtotals applied, you can protect the 
> worksheet
> in code (auto_open/workbook_open??).
>
> Option Explicit
> Sub auto_open()
>    With Worksheets("sheet1")
>        .Protect Password:="hi", userinterfaceonly:=True
>        .EnableOutlining = True
>        .EnableAutoFilter = True
>    End With
> End Sub
>
> It needs to be reset each time you open the workbook.  (excel doesn't 
> remember
> it after closing the workbook.)
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> "glen.e.mettler@lmco.com" wrote:
>>
>> I have a sheet that has rows and columns that are grouped.
>> I need to protect rollup portion of the grouped rows.
>> However, when I do that and protect the sheet, I can no longer group
>> and ungroup.
>> How can I allow the user to group and ungroup rows and columns but
>> protect the grouped row?
>> Example:
>> row 4 is a rollup of MY DATA with the next 10 rows as
>> supporting/detailed data.  I have created a group so that I can
>> contract 9 of the rows and show only the rollup MY DATA or expand it
>> and show all the subordinate data.  The formulas in the columns MY DATA
>> are protected while the cells in the supporting rows are available to
>> the user for change.
>>
>> However, when I protect the sheet, the user can no longer group and
>> ungroup.  How can I fix this?
>>
>> Glen
>
> -- 
>
> Dave Peterson
> 



0
3/17/2006 12:49:34 AM
Reply:

Similar Artilces:

Grouping data
I am trying to group data for a specific event and have not been successful, so I am hoping someone can help. ID Date Value 174 12/3/2007 102 174 12/3/2007 909 174 12/11/2007 405 174 12/11/2007 701 206 12/6/2007 1001 173 12/3/2007 500 I want to group by ID, the MAX Date for that ID and the Max Value for the ID & Max Date The desired result would be: ID Date Value 174 12/11/2007 701 206 12/6/...

How do i enter a note in excel to an unlocked cell on a protected.
I have a protected work sheet that has a range of cells unlocked for users to log into an update. Because the worksheet is protected the user can not add a note to the unlocked cell unless the protection is removed - which defeats the object of protection. Is there a way round this? You may be able to just protect the worksheet and allow the users to edit Objects. In xl2002, tools|protection|protect sheet Near the bottom of the list of what you can allow users to do is: Edit Objects. Earlier versions had the same option as a checkmark ont that worksheet protection dialog box (IIRC). ...

Group Footer at Bottom of Last Page of Group
I would like for a group footer to appear at the bottom of the last page of a group -- either hide the page footer on pages if it is not the last page of the group or have the group footer print at the bottom of the page. I have used the instructions to reset the page number and the total page count for each group. (http://support.microsoft.com/kb/841779/en-us) I was hoping to be able to set it where if the page equals the total pages of the group, it will make the page footer visible. For some reason, if there is more than one page, it doesn't work properly. I have also used the i...

query based distribution group
Hello all running Exchange 2007 sp2, I need to modify an existing query based DL so it finds mailboxes and if the account has two different email address stamped on it. For example if the mailbox has test1.com and test2.com and it's a mailbox then make it a member of the query based DL Thanks for any help If it has those two e-mail addresses? That should return just one recipient. Or do you mean a recipient with any two proxy addresses? I don't believe that's possible with LDAP. Or do you mean a mailbox-enabled user with proxy addresses in two domai...

how do I chart columns, rows and sheets
I want to chart a set of colums and rows which vary from sheet to sheet. Basically three axis. How about two axis, with different series representing different values from sheet to sheet? This page describes a bit about using data from different sheets in a chart: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "yorkieshome" <yorkieshome@discussions.microsoft.com> wrote in message news:E51CB657-0938-4845-80C5-DD7CA8ABD81D@microsof...

Creating Universal Groups with Distribution Group members?
Hello all. We have a Windows 2003 Active Directory with Exchange 2003, and I don't really want to duplicate work, but I think I may have to (ugh!). Here is the deal. I need to create an All Managers Universal Group with every group that has managers in it. So, what I tried doing, was I created a Universal Group and added the _All Managers DL into it -- but that didn't seem to work, according to my boss who asked me to create this UG for him. So, my question is, am I not allowed to add DL members to a Universal Security Group? And if now, is there any way that I can take...

Opening Protected XL Document
Is there ANYWAY that these documents can be opened as read only so you have to save it as something else? ...

Distribution Group list
I have a problem with the distribution group list. Here it goes: I removed and deleted the account from this group list but whenever I send a task, it always tries to reach this deleted account and give me a undeliverable error message. Please help. Thanks Restart Exchange services. Linh wrote: > I have a problem with the distribution group list. Here > it goes: I removed and deleted the account from this > group list but whenever I send a task, it always tries to > reach this deleted account and give me a undeliverable > error message. Please help. Thanks ...

Using Password-protected files for a report
I have many user files (timesheets) and a report file (payroll report) that uses these timesheets as links. The timesheets are password-protected. I need the report to be able to be updated without opening all the files. Is this possible? Thanks! ...

Multiple sheets printed to single page
I have a spreadsheet with 3 sheets. Each sheet is just a small table but the column widths are specific, Each sheet is only 3.5 inches tall. I want to print all three sheets on a single page. How do I do this. I've been all over Excell and cant figure it out. Any attempts to merge the sheets ends up modifieing the column widths. Thanks Hi Docwatmo Try this http://www.rondebruin.nl/print.htm#non-contiguous -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Docwatmo" <nospammy.dwatson@linncountyrec.com> wrote in message news:4654a420$0$504$815e3792@...

Conditional import of Excel sheets
Hi, maybe someone can help me with the following: on a weekly basis I need to import 3 Excel files into an existing empty table (I run a delete query on this table before the import). The Excel files are stored in the same folder every week. Each file has a field showing [Current Year/Week No]. The three files show the current week for this year, previous year and the year before the previous year. How can you instruct MS Access (2003) to look up this field and to import only the approrpiate files (i.e. the files that refer to the reporting week)? I do have a calender function from which the u...

Data, Group got error message Cannot shift object off sheet
I created a command grouping several columns together by going to Data, Group. When I go to click on the "-" sign to group. It gives me an error message;"Cannot shift object off sheet". Why? and how can I fix it. Maybe you have some objects that get rearranged when you hide the columns using your grouping symbols. This may have some suggestions that work for you. It's kind of a similar problem. XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged ...

macro protection
In my macro that I wrote, I have the password to unprotect th worksheet. I would like to keep that a secret. The button a create for my macro is protected, so it cannot be change, but the user of m workbook can go into tools>macros> and edit the macro there. Is there a way to make it so they cannot view the macro code? Data still needs to be entered in places on the worksheet, but I'v designed it so that it can only be entered in specified places -- Message posted from http://www.ExcelForum.com Hi I'm not sure about how it affects the access to code through Tools.Macros.E...

Local Data Groups
Hi, Does anyone know if its possible to create local data groups for offline clients and somehow push the groups out to all users, rather than going to each outlook laptop client? Jo Savidge You can deploy the SFO client using group policies. When the user next logs onto the domain the SFO client will be installed ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm http://www.crowechizek.com/microsoft "Jo Savidge" <JoSavidge@discussions.microsoft.com> wrote in message news:CAE65DA4-C2E3-4F50-A78B-39C98940A129@microsoft.c...

graphing by group
I have 10 individuals in 5 groups. I would like to graph the individuals but keeping them within their groups. When I try to graph it ends up as 10 groups. I would also like to make each group of individuals a different colour. Thanks, Tim Hi Tim In Step 2 of Chart Wizard make sure you have Series by Rows or Series by Columns correctly selected. If you groups are in different columns us Series by Columns other wise use Series by Rows -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Tim Pasma" <anonymous@discussions.microsoft.com> wrote in m...

Reference other sheet
I have a sheet (Study Sheet) that has from 1 to 10 Studies across and 1 to 30 Cases down. Study 1 Study 2 Part Test Results Part Test Results Case 1 1 Case 1 1 2 2 3 3 4 4 Part Test Results Part Test Results Case 2 1 Case 2 1 2 2 3 3 4 4 On a separate work sheet I would like to have a place at the top that I could change the case number and the study number and it would show the date for just that case and study. This way they would not have to look at all of them and get c...

Passing option group value to vba function
I'm sure I've done this before but can't for the life of me figure it out now, and hours of research online haven't helped. I'm simply trying to pass an option group value from a form to a vba function. I'd like the user to choose option1 or option2 and when they click a button, the function will run an if statement like "if option1.value=true then...." I've got 2 options named option1 and option2 and a frame called frame20, what else do I need? I've also tried bounding the selection to a textbox, but keep getting errors. Any help on the process? Tha...

managing distribution groups
Hello all, I am running exchange 2003 and I would like to know is there a way and I can have a regular user maintain my distribtion groups. If so how would I go about that? Thanks so much Mike You can grant permissions at the disty group level to allow ordinary members to add and remove users. Be careful to only do disty groups as security groups changed by users in outlook is a touch insecure. "Mike B" <MikeB@discussions.microsoft.com> wrote in message news:0F2F3709-0900-46B1-B87B-A2449FD44B75@microsoft.com... > Hello all, > > I am running exchange 2003 and...

Secondary Protection for Windows 2003 File Server Cluster
Hi, I am trying to setup Secondary Protection for a Windows File Server Cluster. When I enable secondary protection on the Agents tabs, it goes to an enabled status, however, when I got to setup a Protection Group I cannot browse to the protected resource. It just does not show the clustered resource. When I now go back to check the secondary protection staus on the Agents tab, it has reverted to "No". I am successfully carrying out secondary protection for stand alone file servers off this secondary server, it is us the clustered resources that are an issue. A...

DSum by group
I have a textbox with a DSum function, but would like it to query only within each group. It seems that most other functions (e.g Avg) automatically calculate by group, but DSum doesn't seem to be. =DSum([MKTCAP],"QryFILTER",[MKTCAP]>100) The text box is within the Group Footer, and I'd like it to return a different value below each group. Thanks! "RussG" <RussG@discussions.microsoft.com> wrote in message news:697878DC-521A-4423-BB7F-97089D536EF2@microsoft.com... >I have a textbox with a DSum function, but would like it to query only >within...

Problem with data sheet and linking it to another sheet to make a report
This is kind of hard to explain, but I will try. I'm making a report in excel by linking the report sheet to another sheet that has the data. What I am doing is entering the equal sign, and then clicking on the data in sheet 2 and put them under the labels I have on sheet 1 (the report). So here are the steps so far: 1. I open excel and design a report with lables and field names, now i just need the data for the report 2. I insert a new sheet that has the data. 3. I go back to the first sheet (report) and put an equal sign into one of the fields where I want the data to appear in my r...

Re: Protecting worksheet with grouped cells
I have a worksheet which has rows as well as cloumns grouped. After protecting the worksheet I am unable to expand and collaspe my groups. error:"You cannot use this command on a protected sheet..." Is there a work-around/solution to this problem? You can only enable outlines and have worksheet protection through a macro: With Worksheets("Sheet1") .EnableOutlining = True .Protect UserInterfaceOnly:=True End With The EnableOutlining property (and UserInterfaceOnly) is not saved with the workbook unfortunately, so this code would have to be run ...

Grouping and grouping within groups
I really need help, and help fast, so thank you ahead of time! I nee to know how to group and group within a group. I have been able to d a group, but once I group within the group and try to group anothe group withing that group it combines them. I know I can put a spac between the information so it doesn't combine them but that doesn' seem to be the right way to do things. For instance I have thre different area codes and I want to group them, then within those group I have five different colors of houses in that area code. I want t group the different colors within their area ...

Cell Protection
Hello All - Thanks to all for previous help. New question - How do I format specific cells that will prevent data from being entered into these cells? These cells contain formulas which calculate totals and subtotals, drawing their data from other cells. Thanks, Craig --- Message posted from http://www.ExcelForum.com/ Hi Craig 1. Select all cells for which you want to allow data entries 2. goto 'Format - Cells'. Choose the tab 'Protection' and uncheck the protection for these cells 3. Protect your worksheet (goto 'Tools - Protection') HTH Frank > Hel...

Show Groups as Page Header
I used the report wizard to create a report based on a table. The table has 252 records. When I preview the report, it only shows 242 records. The records are sorted / grouped by district. In the table, the fields are District Business Name DBA Name Amount The wizard placed the District and field names into the District Name Header. To make the report more readable, how can I get the group headers to be the page headers? That is, if a group prints over more than one page, how can I get the page header to show the group name? You can set the "Repeat Section" property of the Gro...