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.
Download Practice Workbook
You can download the workbook and practice with them.
2 Examples of Excel VBA to Protect Sheet but Allow to Select Locked Cells
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.
- 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.
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 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.
This will end the procedure.
- How to Password-Protect Hidden Sheets in Excel (3 Suitable Methods)
- Protect Excel Sheet but Allow Data Entry (2 Handy Approaches)
- How to Protect Excel Sheet from Deleting (3 Simple Methods)
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.
- 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.
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.
Protect the active sheet by the password.
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. Or you can have a glance at our other articles in the ExcelDemy.com blog!