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:
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 🙂 )