Hi
The password should be supplied in the Protect/Unprotect e.g. ActiveSheet.Unprotect ("YourPassword")
Also you should make sure the the ActiveSheet is indeed the "Requirements" sheet.
Del +++
>________________________________
>From:Tony Davis <studiot@gmail.com>
>To:ExcelVBA@yahoogroups.com
>Sent:Friday, 28 October 2011, 10:21
>Subject:[ExcelVBA] How to hide password request box
>
>
>
>Hi all
>
>I have created a worksheet that contains details of uncovered duty sheets
>which is given to staff on a weekly basis so that they can apply for any
>available overtime. On the worksheet I have used a list of the shifts and
>days in custom order, so that all I have to do is select the shift code and
>then the day (both from validated lists) and the times are filled in with
>vlookup. Because previous experience has taught me not to trust the managers
>I have protected the cells so that all they can do is select the day and
>shift code from the drop down arrows. One of the managers has asked that
>they be given the option to sort the list into day order. I have recorded a
>macro that unprotects the sheets, orders the list as requested then re
>protects the sheet. The only problem is that when the macro button is
>pressed Excel shows a box that asks for the password. This isn't needed and
>indeed pressing cancel allows the macro to run, pressing ok without entering
>a password results in a run time error. My question really is, is there
>anyway of stopping the display of the "unprotect worksheet box"?
>
>The macro is as below;
>
>Sub Reorder()
>'
>' Reorder Macro
>' To sort the available shifts into date order
>'
>'
>ActiveSheet.Unprotect
>Range("A5:I51").Select
>ActiveWorkbook.Worksheets("Requirements").Sort.SortFields.Clear
>ActiveWorkbook.Worksheets("Requirements").Sort.SortFields.Add
>Key:=Range( _
>"A6:A51"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=
>_
>"Sun,Mon,Tue,Wed,Thur,Fri,Sat", DataOption:=xlSortNormal
>With ActiveWorkbook.Worksheets("Requirements").Sort
>.SetRange Range("A5:I51")
>.Header = xlYes
>.MatchCase = False
>.Orientation = xlTopToBottom
>.SortMethod = xlPinYin
>.Apply
>End With
>Range("I4").Select
>ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
>Scenarios:=True
>End Sub
>
>Many thanks
>
>Tony
>--
>
>Rompiendo la monotonia del tiempo
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar