Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Hello all,
I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Hi,
The Group and Outlining commands are not available when the spreadsheet is protected, however, you might want to consider using a pivot table since you can protect them but still retain most of their functionality. And they have very nice grouping features. -- Cheers, Shane Devenshire "AccessHelp" wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Shane,
Thanks for the information. Unfortunately, I can not use the pivot table for what I am doing. Thanks. "ShaneDevenshire" wrote: Hi, The Group and Outlining commands are not available when the spreadsheet is protected, however, you might want to consider using a pivot table since you can protect them but still retain most of their functionality. And they have very nice grouping features. -- Cheers, Shane Devenshire "AccessHelp" wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Dave,
Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
One way:
Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Dave,
Thank you very much for the code. Again, you code works perfectly. "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Good morning Dave,
After using your code, I noticed two things. One is when I opened the workbook, I was not prompted to enable the macro (Security Warnings screen). The other one is how does Excel know to execute the code/macro when the workbook is opened. Usually, the user has to run the macro to execute the code. If you could, please share with me those knowledge. Thanks. As always, you are the best! "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Depending on your version of excel, there's a setting that allows you to disable
all macros, allow only signed macros to run, or get prompted to see if you want to allow macros, or allow all macros (with no prompt). In xl2003, it's: tools|macro|security|Security level tab If the user has the highest setting, they won't get prompted at all and macros won't run. If they got a prompt and said no, then the macro won't run. And the name of the macro was special--it's called Auto_Open. This procedure in a general module will run each time you open the workbook (well, and allow macros). (And a procedure named Auto_Close will run when you close the workbook.) There are other ways, too. There's a workbook_Open event that goes under the ThisWorkbook module. They essentially can do the same thing. There are lots of other events that make running macros seem like magic. AccessHelp wrote: Good morning Dave, After using your code, I noticed two things. One is when I opened the workbook, I was not prompted to enable the macro (Security Warnings screen). The other one is how does Excel know to execute the code/macro when the workbook is opened. Usually, the user has to run the macro to execute the code. If you could, please share with me those knowledge. Thanks. As always, you are the best! "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Dave,
Thank you very much for taking the time to explain to me. Where do you think I can find those special macro names (auto_open, etc.)? Thanks. "Dave Peterson" wrote: Depending on your version of excel, there's a setting that allows you to disable all macros, allow only signed macros to run, or get prompted to see if you want to allow macros, or allow all macros (with no prompt). In xl2003, it's: tools|macro|security|Security level tab If the user has the highest setting, they won't get prompted at all and macros won't run. If they got a prompt and said no, then the macro won't run. And the name of the macro was special--it's called Auto_Open. This procedure in a general module will run each time you open the workbook (well, and allow macros). (And a procedure named Auto_Close will run when you close the workbook.) There are other ways, too. There's a workbook_Open event that goes under the ThisWorkbook module. They essentially can do the same thing. There are lots of other events that make running macros seem like magic. AccessHelp wrote: Good morning Dave, After using your code, I noticed two things. One is when I opened the workbook, I was not prompted to enable the macro (Security Warnings screen). The other one is how does Excel know to execute the code/macro when the workbook is opened. Usually, the user has to run the macro to execute the code. If you could, please share with me those knowledge. Thanks. As always, you are the best! "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
The only two that I know of that are in a General module are the Auto_Open and
Auto_Close. But you can find more info about those other events here. Chip Pearson has some instructions on events: http://www.cpearson.com/excel/Events.aspx David McRitchie has some notes, too: http://www.mvps.org/dmcritchie/excel/event.htm And there are other application events, too: Chip Pearson's site: http://www.cpearson.com/excel/AppEvent.aspx AccessHelp wrote: Dave, Thank you very much for taking the time to explain to me. Where do you think I can find those special macro names (auto_open, etc.)? Thanks. "Dave Peterson" wrote: Depending on your version of excel, there's a setting that allows you to disable all macros, allow only signed macros to run, or get prompted to see if you want to allow macros, or allow all macros (with no prompt). In xl2003, it's: tools|macro|security|Security level tab If the user has the highest setting, they won't get prompted at all and macros won't run. If they got a prompt and said no, then the macro won't run. And the name of the macro was special--it's called Auto_Open. This procedure in a general module will run each time you open the workbook (well, and allow macros). (And a procedure named Auto_Close will run when you close the workbook.) There are other ways, too. There's a workbook_Open event that goes under the ThisWorkbook module. They essentially can do the same thing. There are lots of other events that make running macros seem like magic. AccessHelp wrote: Good morning Dave, After using your code, I noticed two things. One is when I opened the workbook, I was not prompted to enable the macro (Security Warnings screen). The other one is how does Excel know to execute the code/macro when the workbook is opened. Usually, the user has to run the macro to execute the code. If you could, please share with me those knowledge. Thanks. As always, you are the best! "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Dave,
Thanks again for the information. Have a great weekend! "Dave Peterson" wrote: The only two that I know of that are in a General module are the Auto_Open and Auto_Close. But you can find more info about those other events here. Chip Pearson has some instructions on events: http://www.cpearson.com/excel/Events.aspx David McRitchie has some notes, too: http://www.mvps.org/dmcritchie/excel/event.htm And there are other application events, too: Chip Pearson's site: http://www.cpearson.com/excel/AppEvent.aspx AccessHelp wrote: Dave, Thank you very much for taking the time to explain to me. Where do you think I can find those special macro names (auto_open, etc.)? Thanks. "Dave Peterson" wrote: Depending on your version of excel, there's a setting that allows you to disable all macros, allow only signed macros to run, or get prompted to see if you want to allow macros, or allow all macros (with no prompt). In xl2003, it's: tools|macro|security|Security level tab If the user has the highest setting, they won't get prompted at all and macros won't run. If they got a prompt and said no, then the macro won't run. And the name of the macro was special--it's called Auto_Open. This procedure in a general module will run each time you open the workbook (well, and allow macros). (And a procedure named Auto_Close will run when you close the workbook.) There are other ways, too. There's a workbook_Open event that goes under the ThisWorkbook module. They essentially can do the same thing. There are lots of other events that make running macros seem like magic. AccessHelp wrote: Good morning Dave, After using your code, I noticed two things. One is when I opened the workbook, I was not prompted to enable the macro (Security Warnings screen). The other one is how does Excel know to execute the code/macro when the workbook is opened. Usually, the user has to run the macro to execute the code. If you could, please share with me those knowledge. Thanks. As always, you are the best! "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
Dave,
I copied what you have below and inserted it into my sheet. I can't get it to work automatically. If I go into macros and tell it to run, then it does great. What am I missing? I am using Excel 2007 Thanks "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Group and Outline on a Protected Sheet
If you copied the code into the worksheet module, then that isn't the correct
location. Move the code into a General module (Insert|Module type). Me wrote: Dave, I copied what you have below and inserted it into my sheet. I can't get it to work automatically. If I go into macros and tell it to run, then it does great. What am I missing? I am using Excel 2007 Thanks "Dave Peterson" wrote: One way: Option Explicit Sub auto_open() dim mySheetNames as variant dim iCtr as long mySheetnames = array("sheet1","sheet2","sheet3","sheet4") for ictr = lbound(mysheetnames) to ubound(mysheetnames) With Worksheets(mysheetnames(ictr)) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub (Untested, uncompiled. Watch for typos.) AccessHelp wrote: Dave, Thank you very much for the code. The code works perfectly. Can you help me one more thing? I have 10 sheets in the workbook, and I would like to use your code for 4 of the 10 sheets. How can I change your code to work on those 4 sheets (e.g. Sheet1, Sheet2, Sheet3 and Sheet4)? I tried to change it to "With Worksheets("Sheet1", "Sheet2", "Sheet3", "Sheet4")". Thanks. "Dave Peterson" wrote: If you already have the outline/subtotals/autofilter 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 'If .FilterMode Then ' .ShowAllData 'End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm AccessHelp wrote: Hello all, I have an Excel sheet that I use the Group (Data|Group and Outline|Group) to group certain rows. If I protect that sheet, I would not able to expand or collapse the groups. I tried turn-on various items (and all items) when I protect the sheet, and it still would not let me expand or collapse. Is there a way I can use the Group on a protected sheet? Thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet protection and "Group and Outline" | Excel Discussion (Misc queries) | |||
Group & Outline in Protected Worksheets | Excel Discussion (Misc queries) | |||
How to Use Group and Outline function when locked sheet | Excel Worksheet Functions | |||
How can I allow an outline to be collapsed on a protected sheet? | Excel Worksheet Functions | |||
Why can't I show or hide rows in an outline on a protected sheet? | Excel Discussion (Misc queries) |