worksheet protection?

[Guest]

I have a worksheet that i have protected with the "select locked cells"
button unchecked. When i close excel and reopen it the worksheet is still
protected but the "select locked cells" button is checked again. This allows
locked cells to be selected.

I want to prevent locked cells from being selected every time i open the
worksheet?
suggestions?

 

[Dave Peterson]

You can do it by protecting the worksheet in code:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi"
.EnableSelection = xlUnlockedCells
End With
End Sub

Excel won't remember these settings after you close it and reopen the workbook
(that's why it's in auto_open).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Tom Ogilvy]

Excel won't remember these settings after you close it and reopen the

workbook

That isn't the case for xl2002 and xl2003 if it is set manually and reopen
it in those versions. This setting will be maintained across opening and
closing the workbook in that case.

However, if you don't use those or your users have earlier versions, then
that would be problematic and you would need to use code.

The problem is that if you use code, the user can disable macros and the
code won't enforce the protection setting.

 

[Dave Peterson]

You are right, sir!

Excel won't remember these settings after you close it and reopen the
workbook

That isn't the case for xl2002 and xl2003 if it is set manually and reopen
it in those versions. This setting will be maintained across opening and
closing the workbook in that case.

However, if you don't use those or your users have earlier versions, then
that would be problematic and you would need to use code.

The problem is that if you use code, the user can disable macros and the
code won't enforce the protection setting.

 

[Guest]

Tom,
I am using excel 2003. I have manually set protection and unchecked "select
locked cells". In a macro on the worksheet I use the following VBA to
unprotect the worksheet:

Windows("PanelSelect.xls").Activate
Sheets("Anagram Entry 1").Select
ActiveSheet.Unprotect Password:="panel"

After the macro performs several things i protect the worksheet with the
following statement:

Windows("PanelSelect.xls").Activate
Sheets("Anagram Entry 1").Select
ActiveSheet.Protect Password:="panel"

Everything seems to work well after the macro is ran. The unprotected cells
are selectable and the protected cells are not. When i save and exit excel
and then reload the panelselect.xls workbook. The protected cells are then
selectable again(which i do not want).

I tried creating a new worksheet without macros. I locked and protected the
cells as described above . I exited the program and re-entered. when i do
this the locked cells remain un-selectable. This leads me to beleive that my
problem is with my macro and the way that i protect my worksheet.

I read the suggestion about auto_open. I am not sure where you would place
such a statement. Does that go inside of the worksheet in question. Do i
place such a statement inside of the macro in question?

Being an untrained novice and starting at ground zero with my project...
some days i feel that i have learned a lot about VBA and excel. And then
there are days like to day when i feel like i know nothing.


dr chuck

Excel won't remember these settings after you close it and reopen the
workbook

That isn't the case for xl2002 and xl2003 if it is set manually and reopen
it in those versions. This setting will be maintained across opening and
closing the workbook in that case.

However, if you don't use those or your users have earlier versions, then
that would be problematic and you would need to use code.

The problem is that if you use code, the user can disable macros and the
code won't enforce the protection setting.

 

[Dave Peterson]

I couldn't duplicate your problem (I also used xl2003).

That setting was remembered when I unprotected, reprotected, closed and saved,
and then reopened.

But if you want to try, that code goes in a General module (change the sheet
name and password, though).

And don't forget to look here:
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Tom,
I am using excel 2003. I have manually set protection and unchecked "select
locked cells". In a macro on the worksheet I use the following VBA to
unprotect the worksheet:

Windows("PanelSelect.xls").Activate
Sheets("Anagram Entry 1").Select
ActiveSheet.Unprotect Password:="panel"

After the macro performs several things i protect the worksheet with the
following statement:

Windows("PanelSelect.xls").Activate
Sheets("Anagram Entry 1").Select
ActiveSheet.Protect Password:="panel"

Everything seems to work well after the macro is ran. The unprotected cells
are selectable and the protected cells are not. When i save and exit excel
and then reload the panelselect.xls workbook. The protected cells are then
selectable again(which i do not want).

I tried creating a new worksheet without macros. I locked and protected the
cells as described above . I exited the program and re-entered. when i do
this the locked cells remain un-selectable. This leads me to beleive that my
problem is with my macro and the way that i protect my worksheet.

I read the suggestion about auto_open. I am not sure where you would place
such a statement. Does that go inside of the worksheet in question. Do i
place such a statement inside of the macro in question?

Being an untrained novice and starting at ground zero with my project...
some days i feel that i have learned a lot about VBA and excel. And then
there are days like to day when i feel like i know nothing.

dr chuck

Excel won't remember these settings after you close it and reopen the
workbook

That isn't the case for xl2002 and xl2003 if it is set manually and reopen
it in those versions. This setting will be maintained across opening and
closing the workbook in that case.

However, if you don't use those or your users have earlier versions, then
that would be problematic and you would need to use code.

The problem is that if you use code, the user can disable macros and the
code won't enforce the protection setting.