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 🙂 )
Hi! I came to your blog from this MSOFFICE thread – https://excel.uservoice.com/forums/304921/suggestions/40858108
I desperately wanted the padlock symbol BACK…..
Was curious about this MACRO. Does it result in the padlock symbol showing up on the tab?
I couldn’t get the Padlock icon itself as it is not in the ascii charset for the font that is used for the tabs. So went with another icon, the one which is shown in the first screenshot of this post.
You can change it to another character if you prefer.