CODEWITHSUNDEEP
vbaexcel
mgcoops
mgcoops

Reputation: 31

Unprotecting then Protecting Sheet

Part 1 I have put together the following code in order to sort data on a sheet.

However, users keep deleting rows from the sheet which ruins the rest of my workbook so I have protected the sheet but then the macro will not longer run.

Can someone help me with the code to unprotect the sheet, run the macro and re protect the sheet at the end?

Sub CustSort1()

    Range("a14").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Sort Key1:=Range("a14"), Order1:=xlAscending, Key2:=Range( _
        "k14"), Order2:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal
    Range("a14").Select

End Sub

I have found similar posts but my level of knowledge is pretty low.

All help appreciated.

Part 2 Ok so small issue has come up! What if I still want the user to be able to insert cells?

I can see I need to use this "AllowInsertingRows" but dont understand where i insert it.

Upvotes: 1

Views: 884

Answers (1)

Jur Pertin
Jur Pertin

Reputation: 564

Absolutely agree with @vba4all. Another way of writing is to unprotect the worksheet before you call CustSort1 sub routine and then protect the worksheet again.

Sub pMainCode()

    'Considering Sheet1 to be where you want to apply sorting
    Worksheets("Sheet1").Unprotect "Password"
    Call CustSort1
    Worksheets("Sheet1").Protect "Password"

End Sub

Upvotes: 1

Related Questions