Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)

While working with Microsoft Excel, we may restrict other people from removing important data on our spreadsheets by safeguarding Excel spreadsheets. But we may need to allow some specific cells to be edited by others. Protecting the sheets and allowing them to edit selected locked cells are important for our daily work with spreadsheets. In this article, we will demonstrate some examples of Excel VBA to protect the sheet and allow select locked cells.

Excel VBA to Protect Sheet but Allow to Select Locked Cells: 2 Examples

Whenever we safeguard a spreadsheet, Excel prevents us from altering any of the cells. When we secure our spreadsheet, nobody can ever change, alter, or remove the material. But we may need some cells to allow selection and edit them.

To protect the sheet and allow select locked cells to edit, we are going to use the dataset below. The dataset contains some employee names in column B, their monthly salary in column C, the incentive they get for a specific month in column D, and their total salary for that particular month in column E.

1. Excel VBA Training Materials & Resources to Protect Sheet but Allow to Select Locked Cells

When dealing with macros, protecting a workbook is especially important since it may help avoid unintentional changes. With Excel VBA, users can easily use the code which acts as excel menus from the ribbon. To use the VBA code to protect the sheet and select locked cells, let’s follow the procedure.

STEPS:

Firstly, go to the Developer tab from the ribbon.
Secondly, from the Code category, click on Visual Basic to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

This will appear in the Visual Basic Editor.
Thirdly, click on Module from the Insert drop-down menu bar.

This will create a Module in your workbook.
And, copy and paste the VBA code shown below.

VBA Code:

Sub Edit_Protected_Locked_Sheet_Cells()
With ThisWorkbook.Worksheets("VBA Material")
.Cells.Locked = True
.Protect
.EnableSelection = xlNoRestrictions
End With
End Sub

After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

This will protect the sheet and allow you to select locked cells to edit.
After running the code, whenever you click on any of the cells in that particular sheet, the Microsoft error message will appear, as shown in the screenshot below. But if you select some cells that will enable the selection and give you no restrictions.

VBA Code Explanation

Sub Edit_Protected_Locked_Sheet_Cells()

Sub is a part of the code that is used to handle the work in the code but will not return any value. It is also known as a subprocedure. So we name our procedure Edit_Protected_Locked_Sheet_Cells().

With ThisWorkbook.Worksheets("VBA Material")
End With

The With expression makes it easy to manage a sequence of statements on a certain object without having to recertify the entity’s description.

.Cells.Locked = True
.Protect

We make the cells locked and protect the whole sheet.

.EnableSelection = xlNoRestrictions

The block of the code will enable the selection and give no restrictions.

End Sub

This will end the procedure.

2. Protect Sheet and Allow to Select Locked Cells Using VBA Macro in Excel

VBA Macros allow us to conduct activities in the same manner over and over again without making any mistakes. It also completes jobs far faster than we could do manually. Let’s see another example to protect sheets that allow select locked cells.

STEPS:

First, go to the Developer tab from the ribbon.
Second, click on Visual Basic to open the Visual Basic Editor.
Another way to open the Visual Basic Editor is simply to press Alt + F11.
Or, right-click on the sheet, then select View Code.
Next, go to Insert and select Module from the drop-down menu.
And, this will open up the visual basic window.
After that, copy and paste the VBA code below.

VBA Code:

Sub Edit_Protected_Locked_Sheet_Cells1()
Dim p As String
Range("C4:E10").Select
Selection.Locked = True
Selection.FormulaHidden = True
p = InputBox("Enter Password")
ActiveSheet.Protect Password:=abc
End Sub

Further, press the F5 key or click on the Run Sub button to run the code.

A pop-up window will appear and ask for the password. Now, enter the password there and click OK.

Now, whenever you want to select the cell range B4:C10, you won’t be allowed to edit them. An error message will appear.

But you will be allowed to select the locked sheet cells from range D4:E10.

VBA Code Explanation

Dim p As String

The DIM statement in VBA refers to “declare,” and it must be used to declare a variable. So, we declare our password which is a string as p.

Range("C4:E10").Select
Selection.Locked = True

Here, we select the range and lock them.

Selection.FormulaHidden = True

We hide the formulas of the spreadsheet.

p = InputBox("Enter Password")

This will create an input box where the box asks for the password.

ActiveSheet.Protect Password:=abc

Protect the active sheet by the password.

Download Practice Workbook

You can download the workbook and practice with them.

Protect Sheet but Allow to Select Locked Cells.xlsm

Conclusion

The above methods will assist you to protect the sheet but allow to select locked cells in Excel VBA. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.

<< Go Back to Protect Excel Sheet | Excel Protect | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)

Excel VBA to Protect Sheet but Allow to Select Locked Cells: 2 Examples

1. Excel VBA Training Materials & Resources to Protect Sheet but Allow to Select Locked Cells

2. Protect Sheet and Allow to Select Locked Cells Using VBA Macro in Excel

Conclusion

Related Articles

Leave a reply Cancel reply