Protect & UnProtect Sheets Excel Sheets using VBA Macros (Excel Online)

Excel Online

With the advent of Modern Browsers and Web Standards, users have switched to online version of Spreadsheets and Word processors which are easily accessible from multiple devices and platforms. But still we can't forget the power of Macros written using VBA Scripting mostly targeted for Excel & Word.

We have been using Excel Spreadsheets as a Database for one of our Sharepoint Application. This Application generates multiple spreadsheets and provides access to users through Excel Online. We want to protect some of the files based on roles and user designation. Looks like currently Sheet protection/UnProtection is not supported in Excel online. This feature is also requested by 183 people in the Excel UserVoice. In the post i am sharing the workaround for the same.

Macro Solution for Protect/UnProtect

The workaround we followed is to Enable the OneDrive Sync in the Sharepoint Application and then create a Macro to Protect/UnProtect all the 100+ of files. This will be immediately synced with OneDrive sync utility to online cloud. When you open the Excel Online and try editing the file, you will get a message "The sheet is protected."

Here the Macro to protect/protect all the files in a given folder in the OneDrive folder.

Function ApplyProtection(sourceFolder as String,sheetName as string, isUnProtect As Boolean)


    Dim sheetPassword As String
    sheetPassword="YOUR SHEET PASSWORD"

    Dim actualFile As String

    Dim selFile As String
    actualFile = Dir(sourceFolder & "\*.xlsx")

    Do While Len(actualFile) > 0

        Application.ScreenUpdating = False
        Set wbResults = Workbooks.Open(filename:=sourceFolder & "/" & actualFile, UpdateLinks:=0, ReadOnly:=False)

        Dim reqSheet As Worksheet

        Set reqSheet = wbResults.Sheets(sheetName)
        Dim sheetState As Boolean

        sheetState = SheetProtected(reqSheet)

        If sheetState = False And isUnProtect = False Then
                reqSheet.Protect Password:=sheetPassword
        End If

        If sheetState = True And isUnProtect = True Then
                reqSheet.Unprotect Password:=sheetPassword
        End If

        actualFile = Dir
    Loop

End Function

​    
Private Function SheetProtected(TargetSheet As Worksheet) As Boolean
     'Function purpose:  To evaluate if a worksheet is protected

    If TargetSheet.ProtectContents = True Then
        SheetProtected = True
    Else
        SheetProtected = False
    End If

End Function

'Function Usage. Create Macro "ProtectFiles" and bind this ProtectAllFiles Module

Sub ProtectAllFiles()

Dim sourceFolder As String
sourceFolder="PUT YOUR SOURCE FOLDER PATH"
Call ApplyProtection(sourceFolder,False)

End Sub


'Function Usage. Create Macro "UnProtectFiles" and bind this UnProtectAllFiles Module
Sub UnProtectAllFiles()

Dim sourceFolder As String
sourceFolder="PUT YOUR SOURCE FOLDER PATH"
Call ApplyProtection(sourceFolder,True)

End Sub

Hope this is helpful for someone who wants to protect sheets for the Online version of Excel.

Happy Scripting.