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.
Top comments (1)
Some comments may only be visible to logged-in visitors. Sign in to view all comments.