Is is possible to collapse and uncollapse outlines data when a worksheet is
protected by clicking on the "+" "-" buttons or the "1" "2" etc buttons. I
have a workbook on which one of the sheets in interactive for people to
complete, however I need to protect some of the cells and then protect the
worksheet so that formulas and some data cannot be amended. To make the
worksheet interface userfriendly, I have outlined some columns and rows.
However, when I protect the worksheet it does not allow me to use this
functionality.

Please can someone help and advise if this function is still possible when
protecting a worksheet, and if so, how?

many thanks
Afsha

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

Afsha wrote:

Is is possible to collapse and uncollapse outlines data when a worksheet is
protected by clicking on the "+" "-" buttons or the "1" "2" etc buttons. I
have a workbook on which one of the sheets in interactive for people to
complete, however I need to protect some of the cells and then protect the
worksheet so that formulas and some data cannot be amended. To make the
worksheet interface userfriendly, I have outlined some columns and rows.
However, when I protect the worksheet it does not allow me to use this
functionality.

Please can someone help and advise if this function is still possible when
protecting a worksheet, and if so, how?

many thanks
Afsha

--

Dave Peterson

Thank you, I will look into this. However, I am setting up a template which I
need to have working and then use this template to build many different
worksheets holding different data for individual employees to complete. Will
they need to enter a password each time? Or, once I set this in my template,
will they get a message to "enable macros" when they open their separate
spreadsheet for it to work without them having to enter a password.

your help is much appreciated.

thanks
Afsha



"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
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

Afsha wrote:

Is is possible to collapse and uncollapse outlines data when a worksheet is
protected by clicking on the "+" "-" buttons or the "1" "2" etc buttons. I
have a workbook on which one of the sheets in interactive for people to
complete, however I need to protect some of the cells and then protect the
worksheet so that formulas and some data cannot be amended. To make the
worksheet interface userfriendly, I have outlined some columns and rows.
However, when I protect the worksheet it does not allow me to use this
functionality.

Please can someone help and advise if this function is still possible when
protecting a worksheet, and if so, how?

many thanks
Afsha

--

Dave Peterson

The password is built into the macro. The user won't know that the code even
uses it.

And if they have their security settings set so that they're prompted each time
excel opens a file with macros, then yep, they'll see that prompt.



Afsha wrote:

Thank you, I will look into this. However, I am setting up a template which I
need to have working and then use this template to build many different
worksheets holding different data for individual employees to complete. Will
they need to enter a password each time? Or, once I set this in my template,
will they get a message to "enable macros" when they open their separate
spreadsheet for it to work without them having to enter a password.

your help is much appreciated.

thanks
Afsha

"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
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

Afsha wrote:

Is is possible to collapse and uncollapse outlines data when a worksheet is
protected by clicking on the "+" "-" buttons or the "1" "2" etc buttons. I
have a workbook on which one of the sheets in interactive for people to
complete, however I need to protect some of the cells and then protect the
worksheet so that formulas and some data cannot be amended. To make the
worksheet interface userfriendly, I have outlined some columns and rows.
However, when I protect the worksheet it does not allow me to use this
functionality.

Please can someone help and advise if this function is still possible when
protecting a worksheet, and if so, how?

many thanks
Afsha

--

Dave Peterson


--

Dave Peterson

thank you - I will try this and see if it works!

Afsha

"Dave Peterson" wrote:

The password is built into the macro. The user won't know that the code even
uses it.

And if they have their security settings set so that they're prompted each time
excel opens a file with macros, then yep, they'll see that prompt.



Afsha wrote:

Thank you, I will look into this. However, I am setting up a template which I
need to have working and then use this template to build many different
worksheets holding different data for individual employees to complete. Will
they need to enter a password each time? Or, once I set this in my template,
will they get a message to "enable macros" when they open their separate
spreadsheet for it to work without them having to enter a password.

your help is much appreciated.

thanks
Afsha

"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
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

Afsha wrote:

Is is possible to collapse and uncollapse outlines data when a worksheet is
protected by clicking on the "+" "-" buttons or the "1" "2" etc buttons. I
have a workbook on which one of the sheets in interactive for people to
complete, however I need to protect some of the cells and then protect the
worksheet so that formulas and some data cannot be amended. To make the
worksheet interface userfriendly, I have outlined some columns and rows.
However, when I protect the worksheet it does not allow me to use this
functionality.

Please can someone help and advise if this function is still possible when
protecting a worksheet, and if so, how?

many thanks
Afsha

--

Dave Peterson


--

Dave Peterson

Please can you explain how this macro works and how I would copy it into
visual basic? Do I copy:
Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

Sorry for the ignorance, but I am not an expert on this and not sure how
this works. Would I need to enter this each time I save a seperate
spreadsheet to send to employees to use? How does it get activated? I would
still need to be able to edit locked cells so need to know how it would be
de-activated. users need to receive spreadsheet protected and only unlocked
cells are where they can edit. However can then also outline and group with
the spreadsheet protected.

thanks
Afsha

"Dave Peterson" wrote:

The password is built into the macro. The user won't know that the code even
uses it.

And if they have their security settings set so that they're prompted each time
excel opens a file with macros, then yep, they'll see that prompt.



Afsha wrote:

Thank you, I will look into this. However, I am setting up a template which I
need to have working and then use this template to build many different
worksheets holding different data for individual employees to complete. Will
they need to enter a password each time? Or, once I set this in my template,
will they get a message to "enable macros" when they open their separate
spreadsheet for it to work without them having to enter a password.

your help is much appreciated.

thanks
Afsha

"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
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

Afsha wrote:

Is is possible to collapse and uncollapse outlines data when a worksheet is
protected by clicking on the "+" "-" buttons or the "1" "2" etc buttons. I
have a workbook on which one of the sheets in interactive for people to
complete, however I need to protect some of the cells and then protect the
worksheet so that formulas and some data cannot be amended. To make the
worksheet interface userfriendly, I have outlined some columns and rows.
However, when I protect the worksheet it does not allow me to use this
functionality.

Please can someone help and advise if this function is still possible when
protecting a worksheet, and if so, how?

many thanks
Afsha

--

Dave Peterson


--

Dave Peterson

This kind of code goes in a general module in the workbook's project.

You'll hae to change the password to match what you're using and you'll have to
change the worksheet name to the name of the sheet you want.

Read David McRitchie's notes to learn about VBA.

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Afsha wrote:

Please can you explain how this macro works and how I would copy it into
visual basic? Do I copy:
Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

Sorry for the ignorance, but I am not an expert on this and not sure how
this works. Would I need to enter this each time I save a seperate
spreadsheet to send to employees to use? How does it get activated? I would
still need to be able to edit locked cells so need to know how it would be
de-activated. users need to receive spreadsheet protected and only unlocked
cells are where they can edit. However can then also outline and group with
the spreadsheet protected.

thanks
Afsha

"Dave Peterson" wrote:

The password is built into the macro. The user won't know that the code even
uses it.

And if they have their security settings set so that they're prompted each time
excel opens a file with macros, then yep, they'll see that prompt.



Afsha wrote:

Thank you, I will look into this. However, I am setting up a template which I
need to have working and then use this template to build many different
worksheets holding different data for individual employees to complete. Will
they need to enter a password each time? Or, once I set this in my template,
will they get a message to "enable macros" when they open their separate
spreadsheet for it to work without them having to enter a password.

your help is much appreciated.

thanks
Afsha

"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
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

Afsha wrote:

Is is possible to collapse and uncollapse outlines data when a worksheet is
protected by clicking on the "+" "-" buttons or the "1" "2" etc buttons. I
have a workbook on which one of the sheets in interactive for people to
complete, however I need to protect some of the cells and then protect the
worksheet so that formulas and some data cannot be amended. To make the
worksheet interface userfriendly, I have outlined some columns and rows.
However, when I protect the worksheet it does not allow me to use this
functionality.

Please can someone help and advise if this function is still possible when
protecting a worksheet, and if so, how?

many thanks
Afsha

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson