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

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

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

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

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

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