How to have a macro protect a worksheet with specific permissions?

[ksantos16]

I have a worksheet on which I need to protect specific cells, yet allow for the users to have all the permissions on the "Protect Sheet" screen, with the exception of anything to do with columns. I also need to allow the users to do a spell check, which gets locked when the sheet is protected. I found a macro that allowed the sheet to be unprotected and protected as part of the macro to allow the spell check to occur. The problem is, when the macro reprotects the sheet, it removes all the permissions except for the standard "select locked cells" and "select unlocked cells". Is there a way that I can have the permissions enabled with the macro?

Here is my macro:

Sub SelectUnlockedCells_Spellcheck()


ActiveSheet.Unprotect Password:="Asyst16"
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "All cells are locked."
    Else
        FoundCells.CheckSpelling CustomDictionary:="CUSTOM.DIC", _
IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=3081
    End If
    
ActiveSheet.Protect Password:="Asyst16"


End Sub

Thank you in advance for any assistance!

 

[Trevor_S]

You just need to amend the final line of your code. Password is only one parameter of Protect, the available ones are listed here: https://msdn.microsoft.com/en-us/library/office/ff840611.aspx
They're optional, with default values. So you need to add whichever ones you need into your final line. For example, if you just want to just allow insertion and deletion of rows, amend your last line of code to:

ActiveSheet.Protect Password:="Asyst16", AllowInsertingRows:=True, AllowDeletingRows:=True

From your question, I suspect that you'll want to add most of them - just do it in the same way, separating each with a comma.

 

[ksantos16]

Thank you, Trevor! I appreciate you help.