another two possibilities:
1. Have a line in the workbook open event:
Worksheets("Requirements").protect userinterfaceonly:=True
(I 'm not sure whether you'll have to supply the password as well.) This will allow vba to manipulate data on that protected sheet without it having to be unprotected and later re-protected.
2. When protecting the sheet there is an option to allow users to sort.
--- In ExcelVBA@yahoogroups.com, Tony Davis <studiot@...> wrote:
>
> 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