Unprotection of outlined data in protected worksheet
 

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

Unprotection of outlined data in protected worksheet
 

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

Unprotection of outlined data in protected worksheet
 

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

Unprotection of outlined data in protected worksheet
 

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

Unprotection of outlined data in protected worksheet
 

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

Unprotection of outlined data in protected worksheet
 

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

Unprotection of outlined data in protected worksheet
 

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

Unprotection of outlined data in protected worksheet
 

OK thanks. I have donee xactly that. Alt-F11. Right clicked on project and
insert and module. I then pasted this:

Option Explicit
Sub auto_open()
With Worksheets("numbers")
.Protect Password:="hello", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

then i clicked save and closed window. I then went to the "numbers"
worksheet. Then on menu clicked on tools,protection,protect sheet and typed
"hello" as password. Sheet now protected. I then go to click on the "+" and
"-" buttons to group or ungroup, however I still get message to saying
worksheet protected? Have I missed a step? Please advise what I am doing
wrong.
thanks again for your help



"Dave Peterson" wrote:

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

Unprotection of outlined data in protected worksheet
 

First, if you included those marks, remove them.

Then do all that same stuff.

But then close that workbook and reopen it.

If you're prompted to allow macros to run, choose yes.

This Auto_Open() procedure runs each time excel opens the file.

If you want, you can also run it yourself (especially for testing).

with your workbook the activeworkbook
Hit tools|macro|macros...
Then click Auto_open
and then click run.

Afsha wrote:

OK thanks. I have donee xactly that. Alt-F11. Right clicked on project and
insert and module. I then pasted this:

Option Explicit
Sub auto_open()
With Worksheets("numbers")
.Protect Password:="hello", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

then i clicked save and closed window. I then went to the "numbers"
worksheet. Then on menu clicked on tools,protection,protect sheet and typed
"hello" as password. Sheet now protected. I then go to click on the "+" and
"-" buttons to group or ungroup, however I still get message to saying
worksheet protected? Have I missed a step? Please advise what I am doing
wrong.
thanks again for your help

"Dave Peterson" wrote:

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


--

Dave Peterson

Unprotection of outlined data in protected worksheet
 

thank you very much for your help, it is now working!

"Dave Peterson" wrote:

First, if you included those marks, remove them.

Then do all that same stuff.

But then close that workbook and reopen it.

If you're prompted to allow macros to run, choose yes.

This Auto_Open() procedure runs each time excel opens the file.

If you want, you can also run it yourself (especially for testing).

with your workbook the activeworkbook
Hit tools|macro|macros...
Then click Auto_open
and then click run.

Afsha wrote:

OK thanks. I have donee xactly that. Alt-F11. Right clicked on project and
insert and module. I then pasted this:

Option Explicit
Sub auto_open()
With Worksheets("numbers")
.Protect Password:="hello", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

then i clicked save and closed window. I then went to the "numbers"
worksheet. Then on menu clicked on tools,protection,protect sheet and typed
"hello" as password. Sheet now protected. I then go to click on the "+" and
"-" buttons to group or ungroup, however I still get message to saying
worksheet protected? Have I missed a step? Please advise what I am doing
wrong.
thanks again for your help

"Dave Peterson" wrote:

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


--

Dave Peterson

Unprotection of outlined data in protected worksheet
 

thank you Dave, this has worked! just one last question. The password to
protect is in the VBA Project module. How can I avoid a technical user to hit
Alt-F11 and see the password?

thanks again!

"Dave Peterson" wrote:

First, if you included those marks, remove them.

Then do all that same stuff.

But then close that workbook and reopen it.

If you're prompted to allow macros to run, choose yes.

This Auto_Open() procedure runs each time excel opens the file.

If you want, you can also run it yourself (especially for testing).

with your workbook the activeworkbook
Hit tools|macro|macros...
Then click Auto_open
and then click run.

Afsha wrote:

OK thanks. I have donee xactly that. Alt-F11. Right clicked on project and
insert and module. I then pasted this:

Option Explicit
Sub auto_open()
With Worksheets("numbers")
.Protect Password:="hello", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

then i clicked save and closed window. I then went to the "numbers"
worksheet. Then on menu clicked on tools,protection,protect sheet and typed
"hello" as password. Sheet now protected. I then go to click on the "+" and
"-" buttons to group or ungroup, however I still get message to saying
worksheet protected? Have I missed a step? Please advise what I am doing
wrong.
thanks again for your help

"Dave Peterson" wrote:

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


--

Dave Peterson

Unprotection of outlined data in protected worksheet
 

Select the project in the VBE
Tools|VBAProject Properties|Protection tab

And give it a memorable password.

But password protection is quite easily broken--this kind of worksheet
protection isn't made to protect intellectual property.

And the password to the project can be broken, too.

If the user is really technical (can he/she search these newsgroups via google),
then your efforts may not be enough to stop them.

Afsha wrote:

thank you Dave, this has worked! just one last question. The password to
protect is in the VBA Project module. How can I avoid a technical user to hit
Alt-F11 and see the password?

thanks again!

"Dave Peterson" wrote:

First, if you included those marks, remove them.

Then do all that same stuff.

But then close that workbook and reopen it.

If you're prompted to allow macros to run, choose yes.

This Auto_Open() procedure runs each time excel opens the file.

If you want, you can also run it yourself (especially for testing).

with your workbook the activeworkbook
Hit tools|macro|macros...
Then click Auto_open
and then click run.

Afsha wrote:

OK thanks. I have donee xactly that. Alt-F11. Right clicked on project and
insert and module. I then pasted this:

Option Explicit
Sub auto_open()
With Worksheets("numbers")
.Protect Password:="hello", userinterfaceonly:=True
.EnableOutlining = True
'.EnableAutoFilter = True
End With
End Sub

then i clicked save and closed window. I then went to the "numbers"
worksheet. Then on menu clicked on tools,protection,protect sheet and typed
"hello" as password. Sheet now protected. I then go to click on the "+" and
"-" buttons to group or ungroup, however I still get message to saying
worksheet protected? Have I missed a step? Please advise what I am doing
wrong.
thanks again for your help

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson