Protect & UnProtect Sheets Excel Sheets using VBA Macros (Excel Online)
Nirmal
Posted on June 29, 2019
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.
Posted on June 29, 2019
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.