Macro to Unprotect and Protect

Hi,

I am having a similar problem but I am not clear if the code applies to my situation or where I would insert the code into my existing macro.

My situation is that I also want to protect my worksheet so that staff don't unwittingly alter codes and values; however, the sheet also has a macro that allows users to select more than one option for one cell. If I protect the worksheet, the macro won't work.

How can I use the macro AND protect the worksheet?

Thanks,
Lyndy

Originally Posted by HaHoBe

Hi, MBeedle,

you may pass the password when protecting and unprotecting the sheet. Please try and alter your password as needed:

Sub unlockrefreshsearchsortlock()
'
' unlockrefreshsearchsortlock Macro
'

'
    ActiveSheet.Unprotect Password:="PW"
    Application.Run "'RSA Agent List - Test.xlsm'!RefreshAndSearch"
    Range("Table_owssvr_2[[RSA LastName]:[Retailer]]").Select
    ActiveWorkbook.Worksheets("owssvr(1)").ListObjects("Table_owssvr_2").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("owssvr(1)").ListObjects("Table_owssvr_2").Sort. _
        SortFields.Add Key:=Range("Table_owssvr_2[RSA LastName]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("owssvr(1)").ListObjects("Table_owssvr_2").Sort. _
        SortFields.Add Key:=Range("Table_owssvr_2[RSA FirstName]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("owssvr(1)").ListObjects("Table_owssvr_2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("Table_owssvr_2[[#Headers],[RSA LastName]]").Select
    ActiveSheet.Protect Password:="PW", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

I think the code may be worked on to simplify it a bit.

Ciao,
Holger