+ Reply to Thread
Results 1 to 3 of 3

Edit existing marco to unlock protect sheet, remove set criteria and re protect sheet

  1. #1
    Registered User
    Join Date
    09-23-2010
    Location
    oxfordshire
    MS-Off Ver
    Excel 2003
    Posts
    72

    Edit existing marco to unlock protect sheet, remove set criteria and re protect sheet

    Iíve inherited this macro ( I know nothing about macros at all) to clear all contents in sheets Mon: Fri from A3:A60


    But I would like to password protect the sheet (Mon to Fri) as several people keep taking the formulas out due to mistakes they make.

    Iíve tried protecting the sheet, then running the macro to see what happens but a run time error 1004 keeps coming up and sort method of range class failed to do with the protection on the sheet.

    Can someone please help with this so when the sheet is protected, when running the macro it will remove the protection on sheets Mon to Fri, remove the contents as below and then re protect sheets Mon to Fri

    Sheets(Array(" MON", " TUE", " WED", " THUR", " FRI")).Select
    ' Sheets(" FRI").Activate
    ' ActiveWindow.LargeScroll Down:=-4
    Range("G3:I60,K3:M60,O3:Q60,S3:U60,W3:Y60").Select
    ' Range("W3").Activate
    Selection.ClearContents
    Sheets(" MON").Select
    Range("A3:E60").Select
    Selection.sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Sheets(" TUE").Select
    Range("A3:E60").Select
    Selection.sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers
    Sheets(" WED").Select
    Range("A3:E60").Select
    Selection.sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers
    Sheets(" THUR").Select
    Range("A3:E60").Select
    Selection.sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers
    Sheets(" FRI").Select
    Range("A3:E60").Select
    Selection.sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers
    ' Range("O60").Select
    ' Sheets(" THUR").Select
    End Sub



    thank you for your help :-)

  2. #2
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Excel 2019
    Posts
    447

    Re: Edit existing marco to unlock protect sheet, remove set criteria and re protect sheet

    Hi

    pls see if it helps
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-23-2010
    Location
    oxfordshire
    MS-Off Ver
    Excel 2003
    Posts
    72

    Re: Edit existing marco to unlock protect sheet, remove set criteria and re protect sheet

    Hello

    Thanks for your help on this, I've input your text underneath sub text but above Sheets(Array(" MON", " TUE", " WED", " THUR", " FRI")).Select


    It seems to work and also take out the info on the Mon sheet that is protected, but still comes up with a run time error 1004 sort method of range class failed. when I select Debug it highlights on the Mon sheet

    Selection.sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    If I select end it all seems ok so am not sure what to do to prevent the notification coming up
    Last edited by summer2010; 08-22-2013 at 09:46 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Marco to Lock/Unlock and Protect/Unprotect Multiple Excel Workbooks
    By Jgonza25 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2013, 03:29 PM
  2. Protect sheet and edit Objects
    By FrankD10 in forum Excel General
    Replies: 3
    Last Post: 08-06-2012, 04:02 PM
  3. Replies: 1
    Last Post: 05-13-2011, 11:25 AM
  4. Macro to unprotect sheet --> unlock cells --> protect again
    By hrhittn23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-02-2011, 07:00 AM
  5. Protect Sheet>Edit Objects except textbox
    By tianah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2009, 12:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1