Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal with the protected spreadsheet
I have a spreadsheet that I need to protect some cells and also to use the
subtotal, but when I protect the spreadsheet, the resource subtotal doesn't work. Does it have someway of using subtotal with a protected spreadsheet? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal with the protected spreadsheet
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, but that won't help when you're filtering via code.) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Daniel Utsch wrote: I have a spreadsheet that I need to protect some cells and also to use the subtotal, but when I protect the spreadsheet, the resource subtotal doesn't work. Does it have someway of using subtotal with a protected spreadsheet? Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal with the protected spreadsheet
Dear Dave Peterson,
Thank you for his help, but I still have one doubts. I don't know a lot on Macro, but I got to apply this macro for 1 spreadsheet. But do I need this resource for 12 spreadsheets, as I do to apply this solution for the 12 spreadsheets? I tried put 1 modulate for every month, but I think in the moment of the opening of the spreadsheet, only the first module is executed. Excuse for my terrible English, because I am Brazilian and I don't understand English very well still. Thanks "Dave Peterson" escreveu: 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, but that won't help when you're filtering via code.) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Daniel Utsch wrote: I have a spreadsheet that I need to protect some cells and also to use the subtotal, but when I protect the spreadsheet, the resource subtotal doesn't work. Does it have someway of using subtotal with a protected spreadsheet? Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal with the protected spreadsheet
When you write about spreadsheets, do you mean separate workbooks/files? Or do
you mean worksheets within a single workbook? If you mean separate workbooks, then you'll want that code in a General module in each of the 12 workbook's projects. If you mean 12 worksheets within the same workbook, then you can use a single procedure, but use different code. If the workbook has exactly 12 sheets and each of those sheets has the same password, then you could use something like: Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next wks End Sub If you have worksheets in that workbook that shouldn't be touched, you could use code like: Option Explicit Sub auto_open() dim iCtr as long dim WksNames as variant wksnames = array("Sheet1", _ "sheet2", _ "Sheet 99", _ "upto12sheets") for ictr = lbound(wksnames) to ubound(wksnames) with thisworkbook.worksheets(wksnames(ictr) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub This version does use the same password (hi) for each of the worksheets. Daniel Utsch wrote: Dear Dave Peterson, Thank you for his help, but I still have one doubts. I don't know a lot on Macro, but I got to apply this macro for 1 spreadsheet. But do I need this resource for 12 spreadsheets, as I do to apply this solution for the 12 spreadsheets? I tried put 1 modulate for every month, but I think in the moment of the opening of the spreadsheet, only the first module is executed. Excuse for my terrible English, because I am Brazilian and I don't understand English very well still. Thanks "Dave Peterson" escreveu: 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, but that won't help when you're filtering via code.) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Daniel Utsch wrote: I have a spreadsheet that I need to protect some cells and also to use the subtotal, but when I protect the spreadsheet, the resource subtotal doesn't work. Does it have someway of using subtotal with a protected spreadsheet? Thanks -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal with the protected spreadsheet
Dave
Thank you very much for the help. I got to conclude my work now. "Dave Peterson" escreveu: When you write about spreadsheets, do you mean separate workbooks/files? Or do you mean worksheets within a single workbook? If you mean separate workbooks, then you'll want that code in a General module in each of the 12 workbook's projects. If you mean 12 worksheets within the same workbook, then you can use a single procedure, but use different code. If the workbook has exactly 12 sheets and each of those sheets has the same password, then you could use something like: Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next wks End Sub If you have worksheets in that workbook that shouldn't be touched, you could use code like: Option Explicit Sub auto_open() dim iCtr as long dim WksNames as variant wksnames = array("Sheet1", _ "sheet2", _ "Sheet 99", _ "upto12sheets") for ictr = lbound(wksnames) to ubound(wksnames) with thisworkbook.worksheets(wksnames(ictr) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub This version does use the same password (hi) for each of the worksheets. Daniel Utsch wrote: Dear Dave Peterson, Thank you for his help, but I still have one doubts. I don't know a lot on Macro, but I got to apply this macro for 1 spreadsheet. But do I need this resource for 12 spreadsheets, as I do to apply this solution for the 12 spreadsheets? I tried put 1 modulate for every month, but I think in the moment of the opening of the spreadsheet, only the first module is executed. Excuse for my terrible English, because I am Brazilian and I don't understand English very well still. Thanks "Dave Peterson" escreveu: 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, but that won't help when you're filtering via code.) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Daniel Utsch wrote: I have a spreadsheet that I need to protect some cells and also to use the subtotal, but when I protect the spreadsheet, the resource subtotal doesn't work. Does it have someway of using subtotal with a protected spreadsheet? Thanks -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
small typo in code
Dave,
you just fix a problem for me as well ! many thanks one small typo tho for any other readers, in the 2nd solution the line that reads; with thisworkbook.worksheets(wksnames(ictr) is missing a closing bracket, i.e. should be with thisworkbook.worksheets(wksnames(ictr)) sure most will spot it as it gives a compile error but might save someone new a bit of head scratching cheers Paul Dave Peterson wrote: Subtotal with the protected spreadsheet 01-Sep-08 When you write about spreadsheets, do you mean separate workbooks/files? Or do you mean worksheets within a single workbook? If you mean separate workbooks, then you'll want that code in a General module in each of the 12 workbook's projects. If you mean 12 worksheets within the same workbook, then you can use a single procedure, but use different code. If the workbook has exactly 12 sheets and each of those sheets has the same password, then you could use something like: Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next wks End Sub If you have worksheets in that workbook that shouldn't be touched, you could use code like: Option Explicit Sub auto_open() dim iCtr as long dim WksNames as variant wksnames = array("Sheet1", _ "sheet2", _ "Sheet 99", _ "upto12sheets") for ictr = lbound(wksnames) to ubound(wksnames) with thisworkbook.worksheets(wksnames(ictr) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub This version does use the same password (hi) for each of the worksheets. Daniel Utsch wrote: -- Dave Peterson Previous Posts In This Thread: On Monday, September 01, 2008 5:26 PM Daniel Utsc wrote: Subtotal with the protected spreadsheet I have a spreadsheet that I need to protect some cells and also to use the subtotal, but when I protect the spreadsheet, the resource subtotal doesn't work. Does it have someway of using subtotal with a protected spreadsheet? Thanks On Monday, September 01, 2008 5:33 PM Dave Peterson wrote: Subtotal with the protected spreadsheet 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, but that won't help when you're filtering via code.) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Daniel Utsch wrote: -- Dave Peterson On Monday, September 01, 2008 7:13 PM DanielUtsc wrote: Subtotal with the protected spreadsheet Dear Dave Peterson, Thank you for his help, but I still have one doubts. I don't know a lot on Macro, but I got to apply this macro for 1 spreadsheet. But do I need this resource for 12 spreadsheets, as I do to apply this solution for the 12 spreadsheets? I tried put 1 modulate for every month, but I think in the moment of the opening of the spreadsheet, only the first module is executed. Excuse for my terrible English, because I am Brazilian and I don't understand English very well still. Thanks "Dave Peterson" escreveu: On Monday, September 01, 2008 8:15 PM Dave Peterson wrote: Subtotal with the protected spreadsheet When you write about spreadsheets, do you mean separate workbooks/files? Or do you mean worksheets within a single workbook? If you mean separate workbooks, then you'll want that code in a General module in each of the 12 workbook's projects. If you mean 12 worksheets within the same workbook, then you can use a single procedure, but use different code. If the workbook has exactly 12 sheets and each of those sheets has the same password, then you could use something like: Option Explicit Sub auto_open() dim wks as worksheet for each wks in thisworkbook.worksheets with wks .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next wks End Sub If you have worksheets in that workbook that shouldn't be touched, you could use code like: Option Explicit Sub auto_open() dim iCtr as long dim WksNames as variant wksnames = array("Sheet1", _ "sheet2", _ "Sheet 99", _ "upto12sheets") for ictr = lbound(wksnames) to ubound(wksnames) with thisworkbook.worksheets(wksnames(ictr) .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True '.EnableAutoFilter = True 'If .FilterMode Then ' .ShowAllData 'End If End With next ictr End Sub This version does use the same password (hi) for each of the worksheets. Daniel Utsch wrote: -- Dave Peterson On Monday, September 01, 2008 9:08 PM DanielUtsc wrote: Subtotal with the protected spreadsheet Dave Thank you very much for the help. I got to conclude my work now. "Dave Peterson" escreveu: EggHeadCafe - Software Developer Portal of Choice Spambot Killer ASP.NET Mailto: Hyperlink Control http://www.eggheadcafe.com/tutorials...aspnet-ma.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to SUM in a protected spreadsheet | Excel Worksheet Functions | |||
Subtotal-new spreadsheet at each break? | Excel Worksheet Functions | |||
Skip Copying a protected cell with subtotal | Excel Discussion (Misc queries) | |||
Formatting in a Protected Spreadsheet | Excel Worksheet Functions | |||
How can I use spellcheck on a spreadsheet that is protected? | Excel Worksheet Functions |