I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub



"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson

Those macros you posted don't run automatically.

Any chance you have a "helpful" addin that's sticking it's head in?

Since it worked ok in safe mode with macros disabled, I'd keep looking for
macros.

Chip Pearson has some notes on how to diagnose startup errors at:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes at:
http://www.jkp-ads.com/Articles/StartupProblems.htm

Essentially, you move all of the stuff out of XLStart and you turn off all the
addins under Toools|addins.

Then you start adding things back one at a time and testing each time to see if
the problem is fixed. When you add something back that causes the problem to
come back, you'll have to decide what to do with that addin.

(Make sure you keep track of all the stuff you turn off and move.)

Roy wrote:

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I think I figured out my problem. It appears as if when I run the Protect
macro a range of cells is unlocked when the sheet is protected. Can I insert
a line in the Protect macro that will delete this range? If not, then I'll
have to go in and delete this range in each of my 1200 sheets.

"Dave Peterson" wrote:

Those macros you posted don't run automatically.

Any chance you have a "helpful" addin that's sticking it's head in?

Since it worked ok in safe mode with macros disabled, I'd keep looking for
macros.

Chip Pearson has some notes on how to diagnose startup errors at:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes at:
http://www.jkp-ads.com/Articles/StartupProblems.htm

Essentially, you move all of the stuff out of XLStart and you turn off all the
addins under Toools|addins.

Then you start adding things back one at a time and testing each time to see if
the problem is fixed. When you add something back that causes the problem to
come back, you'll have to decide what to do with that addin.

(Make sure you keep track of all the stuff you turn off and move.)

Roy wrote:

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Before you do that, just a comment...

xl2002 added a feature that allowed developers to protect a worksheet, but allow
users to edit ranges. If you use this feature, you may not want to delete
ranges.

But I'm not sure what you mean by delete a range.

You could use:

wks.range("a1:B9").clear
wks.range("a1:b9").clearcontents
wks.range("a1:b9").entirerow.delete
wks.range("a1:b9").entirecolumn.delete
wks.range("a1:b9").delete shift:=xlup

or something else???



Roy wrote:

I think I figured out my problem. It appears as if when I run the Protect
macro a range of cells is unlocked when the sheet is protected. Can I insert
a line in the Protect macro that will delete this range? If not, then I'll
have to go in and delete this range in each of my 1200 sheets.

"Dave Peterson" wrote:

Those macros you posted don't run automatically.

Any chance you have a "helpful" addin that's sticking it's head in?

Since it worked ok in safe mode with macros disabled, I'd keep looking for
macros.

Chip Pearson has some notes on how to diagnose startup errors at:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes at:
http://www.jkp-ads.com/Articles/StartupProblems.htm

Essentially, you move all of the stuff out of XLStart and you turn off all the
addins under Toools|addins.

Then you start adding things back one at a time and testing each time to see if
the problem is fixed. When you add something back that causes the problem to
come back, you'll have to decide what to do with that addin.

(Make sure you keep track of all the stuff you turn off and move.)

Roy wrote:

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Sorry, what I meant is that I want to remove the users ability to edit the
range. I don't want to delete the range.

"Dave Peterson" wrote:

Before you do that, just a comment...

xl2002 added a feature that allowed developers to protect a worksheet, but allow
users to edit ranges. If you use this feature, you may not want to delete
ranges.

But I'm not sure what you mean by delete a range.

You could use:

wks.range("a1:B9").clear
wks.range("a1:b9").clearcontents
wks.range("a1:b9").entirerow.delete
wks.range("a1:b9").entirecolumn.delete
wks.range("a1:b9").delete shift:=xlup

or something else???



Roy wrote:

I think I figured out my problem. It appears as if when I run the Protect
macro a range of cells is unlocked when the sheet is protected. Can I insert
a line in the Protect macro that will delete this range? If not, then I'll
have to go in and delete this range in each of my 1200 sheets.

"Dave Peterson" wrote:

Those macros you posted don't run automatically.

Any chance you have a "helpful" addin that's sticking it's head in?

Since it worked ok in safe mode with macros disabled, I'd keep looking for
macros.

Chip Pearson has some notes on how to diagnose startup errors at:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes at:
http://www.jkp-ads.com/Articles/StartupProblems.htm

Essentially, you move all of the stuff out of XLStart and you turn off all the
addins under Toools|addins.

Then you start adding things back one at a time and testing each time to see if
the problem is fixed. When you add something back that causes the problem to
come back, you'll have to decide what to do with that addin.

(Make sure you keep track of all the stuff you turn off and move.)

Roy wrote:

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Does this mean you're running xl2002+?

If yes, then record a macro when you use
tools|protection|allow users to edit ranges.

You'll see the code that allows this.

But in my simple testing, I could unprotect a worksheet that I applied this
"range editting" and then reprotect without losing that ability.

Any chance you (and your users) are using multiple versions of excel?

This stuff only works in xl2002+.

Roy wrote:

Sorry, what I meant is that I want to remove the users ability to edit the
range. I don't want to delete the range.

"Dave Peterson" wrote:

Before you do that, just a comment...

xl2002 added a feature that allowed developers to protect a worksheet, but allow
users to edit ranges. If you use this feature, you may not want to delete
ranges.

But I'm not sure what you mean by delete a range.

You could use:

wks.range("a1:B9").clear
wks.range("a1:b9").clearcontents
wks.range("a1:b9").entirerow.delete
wks.range("a1:b9").entirecolumn.delete
wks.range("a1:b9").delete shift:=xlup

or something else???



Roy wrote:

I think I figured out my problem. It appears as if when I run the Protect
macro a range of cells is unlocked when the sheet is protected. Can I insert
a line in the Protect macro that will delete this range? If not, then I'll
have to go in and delete this range in each of my 1200 sheets.

"Dave Peterson" wrote:

Those macros you posted don't run automatically.

Any chance you have a "helpful" addin that's sticking it's head in?

Since it worked ok in safe mode with macros disabled, I'd keep looking for
macros.

Chip Pearson has some notes on how to diagnose startup errors at:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes at:
http://www.jkp-ads.com/Articles/StartupProblems.htm

Essentially, you move all of the stuff out of XLStart and you turn off all the
addins under Toools|addins.

Then you start adding things back one at a time and testing each time to see if
the problem is fixed. When you add something back that causes the problem to
come back, you'll have to decide what to do with that addin.

(Make sure you keep track of all the stuff you turn off and move.)

Roy wrote:

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

We are all using Excel 2003. In the macro I'm currently using is there a way
to disable or remove the users ability to edit the range? I recorded a macro
and inserted it into my existing macro but it does not work because it can't
compile AllowEditRanges.

"Dave Peterson" wrote:

Does this mean you're running xl2002+?

If yes, then record a macro when you use
tools|protection|allow users to edit ranges.

You'll see the code that allows this.

But in my simple testing, I could unprotect a worksheet that I applied this
"range editting" and then reprotect without losing that ability.

Any chance you (and your users) are using multiple versions of excel?

This stuff only works in xl2002+.

Roy wrote:

Sorry, what I meant is that I want to remove the users ability to edit the
range. I don't want to delete the range.

"Dave Peterson" wrote:

Before you do that, just a comment...

xl2002 added a feature that allowed developers to protect a worksheet, but allow
users to edit ranges. If you use this feature, you may not want to delete
ranges.

But I'm not sure what you mean by delete a range.

You could use:

wks.range("a1:B9").clear
wks.range("a1:b9").clearcontents
wks.range("a1:b9").entirerow.delete
wks.range("a1:b9").entirecolumn.delete
wks.range("a1:b9").delete shift:=xlup

or something else???



Roy wrote:

I think I figured out my problem. It appears as if when I run the Protect
macro a range of cells is unlocked when the sheet is protected. Can I insert
a line in the Protect macro that will delete this range? If not, then I'll
have to go in and delete this range in each of my 1200 sheets.

"Dave Peterson" wrote:

Those macros you posted don't run automatically.

Any chance you have a "helpful" addin that's sticking it's head in?

Since it worked ok in safe mode with macros disabled, I'd keep looking for
macros.

Chip Pearson has some notes on how to diagnose startup errors at:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes at:
http://www.jkp-ads.com/Articles/StartupProblems.htm

Essentially, you move all of the stuff out of XLStart and you turn off all the
addins under Toools|addins.

Then you start adding things back one at a time and testing each time to see if
the problem is fixed. When you add something back that causes the problem to
come back, you'll have to decide what to do with that addin.

(Make sure you keep track of all the stuff you turn off and move.)

Roy wrote:

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I don't understand about the not compiling if you recorded in xl2003.

But this one line removed the first range:

ActiveSheet.Protection.AllowEditRanges(1).Delete

This would remove them all:

Dim myAllowEditRange As AllowEditRange
Dim wks As Worksheet
Set wks = ActiveSheet
For Each myAllowEditRange In wks.Protection.AllowEditRanges
myAllowEditRange.Delete
Next myAllowEditRange



Roy wrote:

We are all using Excel 2003. In the macro I'm currently using is there a way
to disable or remove the users ability to edit the range? I recorded a macro
and inserted it into my existing macro but it does not work because it can't
compile AllowEditRanges.

"Dave Peterson" wrote:

Does this mean you're running xl2002+?

If yes, then record a macro when you use
tools|protection|allow users to edit ranges.

You'll see the code that allows this.

But in my simple testing, I could unprotect a worksheet that I applied this
"range editting" and then reprotect without losing that ability.

Any chance you (and your users) are using multiple versions of excel?

This stuff only works in xl2002+.

Roy wrote:

Sorry, what I meant is that I want to remove the users ability to edit the
range. I don't want to delete the range.

"Dave Peterson" wrote:

Before you do that, just a comment...

xl2002 added a feature that allowed developers to protect a worksheet, but allow
users to edit ranges. If you use this feature, you may not want to delete
ranges.

But I'm not sure what you mean by delete a range.

You could use:

wks.range("a1:B9").clear
wks.range("a1:b9").clearcontents
wks.range("a1:b9").entirerow.delete
wks.range("a1:b9").entirecolumn.delete
wks.range("a1:b9").delete shift:=xlup

or something else???



Roy wrote:

I think I figured out my problem. It appears as if when I run the Protect
macro a range of cells is unlocked when the sheet is protected. Can I insert
a line in the Protect macro that will delete this range? If not, then I'll
have to go in and delete this range in each of my 1200 sheets.

"Dave Peterson" wrote:

Those macros you posted don't run automatically.

Any chance you have a "helpful" addin that's sticking it's head in?

Since it worked ok in safe mode with macros disabled, I'd keep looking for
macros.

Chip Pearson has some notes on how to diagnose startup errors at:
http://www.cpearson.com/excel/StartupErrors.htm

And Jan Karel Pieterse has more notes at:
http://www.jkp-ads.com/Articles/StartupProblems.htm

Essentially, you move all of the stuff out of XLStart and you turn off all the
addins under Toools|addins.

Then you start adding things back one at a time and testing each time to see if
the problem is fixed. When you add something back that causes the problem to
come back, you'll have to decide what to do with that addin.

(Make sure you keep track of all the stuff you turn off and move.)

Roy wrote:

Dave,

Thanks for all your help with this. I looked at each one of my worksheets
modules and ThisWorkbook module and they are all blank. The only macros I
have are in Module1 and they are ones I copied on here on a previous message.

I ran excel in safe mode as you suggested and the sheets were still
protected after I reopened the file in safe mode.

I'm not sure what else to do. I guess I'll have to run a macro when the
file is opened that protects the worksheets but how do I do that without
having to type in the password and having this macro not affect my other
protect all macro?

Roy

"Dave Peterson" wrote:

And you looked under the ThisWorkbook module for a Workbook_open routine that
might call the Unprotect_all subroutine (or even workbook_beforesave)????

And remember to check under the each worksheet, too. There could be code inside
one of those modules that calls that routine.

If that doesn't work, you can try this to eliminate the macro possibility:

Close excel
windows start button|run
excel /safe
(this'll start excel in safe mode and won't allow macros to run).

File|Open your workbook.

Protect the worksheets (manually).

File|Save
File|Close

And then reopen while you're still in safe mode.

If the worksheets are still protected, I'd keep looking for a macro problem.

Roy wrote:

I don't think so to either question. I only have one file and eventhough I
do have macros in the workbook to unprotect and protect, I did not make them
run when the workbook is opened. I modified the macros that I found on this
site. Here are the macros I'm using:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
Prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
Prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
wks.EnableSelection = xlUnlockedCells
wks.Protect vPword
Next
End Sub

"Dave Peterson" wrote:

This isn't standard behavior.

Any chance you're opening the wrong workbook?

Or your workbook opens and a macro runs that does the unprotecting?

Roy wrote:

I have a sheet with protected cells. Everytime I re-open the file, the cells
become unprotected. How do I stop the cells from becoming unprotected?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson