Protect sheets w/o user password prompt

[hotherps]

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

 

[Paul B]

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]

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

 

[Paul B]

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]

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

 

[hotherps]

Please disregard my previous post, thanks for all who helpe

 

[Paul B]

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