Protect sheets w/o user password prompt
 

When I run this code in the open workbook event. It locks all of th
formulas but still allows the use of command buttons.

But if I uncomment the line below it promps the user for a passwor
once for each sheet in the workbook(9 shts)

If I comment it out it protects everything I want but protection can b
turned off without a password

Dim Sht As Worksheet
Const Pass As String = "hello"

On Error Resume Next
For Each Sht In ThisWorkbook.Worksheets
With Sht
.Protect UserInterFaceOnly:=True
'.Unprotect Password:=Pass
.cells.Locked = False
.cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect Password:=Pass
End With
Next Sh

--
hotherp
-----------------------------------------------------------------------
hotherps's Profile: http://www.excelforum.com/member.php...nfo&userid=505
View this thread: http://www.excelforum.com/showthread.php?threadid=32005

Protect sheets w/o user password prompt
 

Try this,

Dim Sht As Worksheet
Const Pass As String = "hello"

On Error Resume Next
For Each Sht In ThisWorkbook.Worksheets
With Sht
..Cells.Locked = False
..Cells.SpecialCells(xlCellTypeFormulas).Locked = True
..Protect UserInterfaceOnly:=True, Password:=Pass

End With
Next Sht


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"hotherps" wrote in message
...

When I run this code in the open workbook event. It locks all of the
formulas but still allows the use of command buttons.

But if I uncomment the line below it promps the user for a password
once for each sheet in the workbook(9 shts)

If I comment it out it protects everything I want but protection can be
turned off without a password

Dim Sht As Worksheet
Const Pass As String = "hello"

On Error Resume Next
For Each Sht In ThisWorkbook.Worksheets
With Sht
Protect UserInterFaceOnly:=True
'.Unprotect Password:=Pass
cells.Locked = False
cells.SpecialCells(xlCellTypeFormulas).Locked = True
Protect Password:=Pass
End With
Next Sht


--
hotherps
------------------------------------------------------------------------
hotherps's Profile:
http://www.excelforum.com/member.php...fo&userid=5055
View this thread: http://www.excelforum.com/showthread...hreadid=320053

Protect sheets w/o user password prompt
 

Thanks that worked but with one strange result. I have a command butto
that sorts by Col A, if the sheet is protected it sorts by Col B?

I've checked the code and can't imagine what would make it do that.

Thank

--
hotherp
-----------------------------------------------------------------------
hotherps's Profile: http://www.excelforum.com/member.php...nfo&userid=505
View this thread: http://www.excelforum.com/showthread.php?threadid=32005

Protect sheets w/o user password prompt
 

Post the code and someone will most likely let you know why it is doing that
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"hotherps" wrote in message
...

Thanks that worked but with one strange result. I have a command button
that sorts by Col A, if the sheet is protected it sorts by Col B?

I've checked the code and can't imagine what would make it do that.

Thanks


--
hotherps
------------------------------------------------------------------------
hotherps's Profile:
http://www.excelforum.com/member.php...fo&userid=5055
View this thread: http://www.excelforum.com/showthread...hreadid=320053

Protect sheets w/o user password prompt
 

This is the code that locks the workbook

Dim Sht As Worksheet
Const Pass As String = "hello"

On Error Resume Next
For Each Sht In ThisWorkbook.Worksheets
With Sht
.cells.Locked = False
.cells.SpecialCells(xlCellTypeFormulas).Locked = True
.Protect UserInterfaceOnly:=True, Password:=Pass

End With
Next Sht


This is the sort code

Range("A4:AY298").Select
Selection.SORT Key1:=Range("A4"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal

Range("A4").Selec

--
hotherp
-----------------------------------------------------------------------
hotherps's Profile: http://www.excelforum.com/member.php...nfo&userid=505
View this thread: http://www.excelforum.com/showthread.php?threadid=32005

Protect sheets w/o user password prompt
 

Please disregard my previous post, thanks for all who helpe

--
hotherp
-----------------------------------------------------------------------
hotherps's Profile: http://www.excelforum.com/member.php...nfo&userid=505
View this thread: http://www.excelforum.com/showthread.php?threadid=32005

Protect sheets w/o user password prompt
 

I do not see how it would sort by column B with this code, also there is no
need to select the range, you could do it like this

Range("A4:AY298").Sort Key1:=Range("A4"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"hotherps" wrote in message
...

This is the code that locks the workbook

Dim Sht As Worksheet
Const Pass As String = "hello"

On Error Resume Next
For Each Sht In ThisWorkbook.Worksheets
With Sht
cells.Locked = False
cells.SpecialCells(xlCellTypeFormulas).Locked = True
Protect UserInterfaceOnly:=True, Password:=Pass

End With
Next Sht


This is the sort code

Range("A4:AY298").Select
Selection.SORT Key1:=Range("A4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Range("A4").Select


--
hotherps
------------------------------------------------------------------------
hotherps's Profile:
http://www.excelforum.com/member.php...fo&userid=5055
View this thread: http://www.excelforum.com/showthread...hreadid=320053