Unconventional use of Excel Sheet Protection Feature

In my previous article, I have explained how you can use data validation to add an awesome feature to your regular dashboard. Today I will explain to you, how to make it more elegant. We will also solve a problem today using the same data used for the data validation article.

So, we all know that Excel has an awesome feature call protection, as per your requirement you can use that feature to protect the sheet, which will not allow the end-user to make any change in the protected excel sheet. Now there is a problem, you can not use the data validation feature in a protected worksheet. The below sheet is protected (Notice Ribbon, all options are disabled)

Even if we remove protection from the cell A1, validation dropdown, the data in the below table will not get updated. Let's see. To remove protection from a single cell, Unprotect Sheet > CTRL+1 > Protection tab > Uncheck the box (Marked yellow)

This will allow you to access Cell A1, even if the sheet is protected (Notice Ribbon, all options are disabled, also notice the formula bar, we can access cell A1)

However, the rest of the sheet is protected. Hence, choosing values from the dropdown will not have any impact on the table or the chart. It will tell you that it can't make any change to a protected sheet. Now, it is a problem, we need to update the data also, we need to restrict the end-user to make any change in the table or the chart. How to do that?

Now, if you remember, in our previous article, we have created a small macro to update the table, we will just add two more lines to that macro.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Application.Intersect(Target, Range("A1")) Is Nothing Then GoTo ex

'before performing the task, the sheet will be unprotected
Sheets("Master_View").Unprotect Password:=1234
Dim str As String
str = Application.WorksheetFunction.VLookup(Range("A1").Value, Sheets(2).Range("A:B"), 2, 0)


ThisWorkbook.Worksheets(str).Select
ActiveSheet.Range("A1:F5").Copy Sheets(1).Range("A3")
Sheets(1).Select
'after completing the task, the sheet will be protected agaian.
Sheets("Master_View").Protect Password:=1234
ex:
Application.ScreenUpdating = True


End Sub

So, every time you are choosing a value from the dropdown, this macro is getting triggered and it is first unprotecting the sheet using Sheets("Master_View").Unprotect Password:=1234 , then after the task is complete, it is again protecting the sheet using Sheets("Master_View").Protect Password:=1234. Basically, copying the data in the table and updating the chart is happening between these two lines of codes. So, our problem is resolved right. The end-user is restricted from making any update in the sheet but he/she can use the dropdown to navigate the dashboard. Is not this amazing? Please leave your feedback. Until then "Stay safe".