Excel Protection Icon on worksheets

About two months ago, the Excel team added a new feature to Excel 365 on the (windows) desktop. They added a padlock icon in front of the worksheet name if the worksheet was protected. I and many others protested this change, and requested it be removed or made optional.
I won’t go into the details of why, but you can read some of the arguments on the Excel uservoice site here:
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/40858108-remove-the-lock-symbols-from-the-tabs-of-protected

After quite a few votes, and people writing some angry posts, Microsoft decided to remove the padlock icon for the time being, while evaluation options. This quickly led to several people in the afore mentioned uservoice suggestion to request that the padlock be added back again, or be made optional.

One of the things I love about the Office suite is VBA. I can use it not only to create fancy applications, but I can also use it to assist me while developing. So for instance, I came up with the idea that we could place some code in the personal Macro Workbook and call it to show/hide the padlock icons. So I quickly wrote 2 procs, one to show the icon and one to hide it. Now I couldn’t do a padlock icon, because I’m limited by the font that excel uses for its tab, so I simply used another symbol in the character set, see below:

The code, placed in a standard module in the personal macro workbook is simple (doesn’t support protected workbooks yet)

Option Explicit
Private Const protectchar As Integer = 1421 ' 8471

Public Sub Display_Protection()
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Worksheets
      If ws.Visible = xlSheetVisible Then
         If ws.ProtectContents = True Then
            'It is protected. Add char if neede
            If Left(ws.Name, 1) <> ChrW(protectchar) Then
               ws.Name = ChrW(protectchar) & " " & ws.Name
            End If
         Else
            'It is not protected. Remove char if present
            If Left(ws.Name, 1) = ChrW(protectchar) Then
               ws.Name = Trim(Mid(ws.Name, 2))
            End If
         End If
      End If
   Next
End Sub
Public Sub Hide_Protection()
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Worksheets
      If Left(ws.Name, 1) = ChrW(protectchar) Then
         ws.Name = Trim(Mid(ws.Name, 2))
      End If
   Next
End Sub

Finally, I added 2 buttons to my ribbon (I added them to the Developer Tab), and assigned the macros to them, along with some icons:

So now, when I click the Display Protection, all protected sheets are marked with the symbol I choose. And when I click Hide Protection, all the symbols are removed once again. Yes, I realize its not the same as the icon dynamically appearing, but on the plus side its now a setting I can control myself.

If this post helped you, please take the time to leave a comment. A little comment gives a lot of motivation.

This is one the things I love about Office. That we can take the same code that we use for our applications, and use it to modify our coding environment (to a degree 🙂 )