Hi Del
Thanks for that
Tony
On 28 October 2011 13:59, Derek Turner <g4swy@yahoo.com> wrote:
> **
>
>
> 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]
>
>
>
--
Rompiendo la monotonia del tiempo
[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