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:
Re: Subtotal with the protected spreadsheet
01-Sep-08
When you write about spreadsheets, do you mean separate workbooks/files? Or d
you mean worksheets within a single workbook
If you mean separate workbooks, then you'll want that code in a General modul
in each of the 12 workbook's projects
If you mean 12 worksheets within the same workbook, then you can use a singl
procedure, but use different code
If the workbook has exactly 12 sheets and each of those sheets has the sam
password, then you could use something like
Option Explici
Sub auto_open(
dim wks as workshee
for each wks in thisworkbook.worksheet
with wk
.Protect Password:="hi", userinterfaceonly:=Tru
.EnableOutlining = Tru
'.EnableAutoFilter = True
'If .FilterMode The
' .ShowAllDat
'End I
End Wit
next wk
End Su
If you have worksheets in that workbook that shouldn't be touched, you could us
code like
Option Explici
Sub auto_open(
dim iCtr as lon
dim WksNames as varian
wksnames = array("Sheet1",
"sheet2",
"Sheet 99",
"upto12sheets"
for ictr = lbound(wksnames) to ubound(wksnames
with thisworkbook.worksheets(wksnames(ictr
.Protect Password:="hi", userinterfaceonly:=Tru
.EnableOutlining = Tru
'.EnableAutoFilter = True
'If .FilterMode The
' .ShowAllDat
'End I
End Wit
next ict
End Su
This version does use the same password (hi) for each of the worksheets
Daniel Utsch wrote
--
Dave Peterson
Previous Posts In This Thread:
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
Re: Subtotal with the protected spreadsheet
If you already have the outline/subtotals/autofilter applied, you can protec
the worksheet in code (auto_open/workbook_open??)
Option Explici
Sub auto_open(
With Worksheets("sheet1"
.Protect Password:="hi", userinterfaceonly:=Tru
.EnableOutlining = Tru
'.EnableAutoFilter = True
'If .FilterMode The
' .ShowAllDat
'End I
End Wit
End Su
It needs to be reset each time you open the workbook. (Earlier versions o
excel don't remember it after closing the workbook. IIRC, xl2002+ will remembe
the allow autofilter setting under tools|Protection|protect sheet, but tha
won't help when you're filtering via code.
If you're new to macros
Debra Dalgleish has some notes how to implement macros here
http://www.contextures.com/xlvba01.htm
David McRitchie has an intro to macros
http://www.mvps.org/dmcritchie/excel/getstarted.ht
Ron de Bruin's intro to macros
http://www.rondebruin.nl/code.ht
(General, Regular and Standard modules all describe the same thing.
Daniel Utsch wrote
--
Dave Peterson
Re: 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:
Re: 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
Re: 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/tutorial...97-f0235cdcb480/spambot-killer-aspnet-ma.aspx