Subtotal with the protected spreadsheet


D

Daniel Utsch

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
 
Ad

Advertisements

D

Dave Peterson

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 here:
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.)
 
D

Daniel Utsch

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:
 
D

Dave Peterson

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.
 
D

Daniel Utsch

Dave

Thank you very much for the help. I got to conclude my work now.

"Dave Peterson" escreveu:
 
Ad

Advertisements

P

Paul Frank

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top