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]
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