Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I protect a number of sheets allowing the user to add a com | New Users to Excel | |||
password protect sheets | Excel Worksheet Functions | |||
Protect Excel sheets with Password Using VBA | Excel Programming | |||
Protecting Multiple sheets with prompt for password to unprotect | Excel Programming |