HOW TO LOCK INDIVIDUAL CELLS IN EXCEL 2013 PASSWORD

For that, re-enter the same password and click on Ok. Once we click on the Unprotect Sheet, it will again ask for the same password to unprotect it. Hence, our column data is now protected successfully.įor unprotecting it again, go to the Review tab and click on the Unprotect Sheet, as shown below. For checking it, try to go in edit mode for any cell of that column (By Pressing F2 or double click) if it is protected, we will see a warning message as below. We had chosen Column A named as Product Type as required. Let’s check whether the column is actually protected now. Now selected Column is locked, and no one can change anything in it without disabling the column’s protection. Once clicked on OK, the system will ask you to re-enter the password again.

Once we are done with selecting the options for protecting the sheet or column, click on OK as highlighted below.

For example, I have checked selective locked cells and selected unlocked cells (which sometimes comes as default).

And check (tick) the options available in a box as per requirement.

Once we click on Protect Sheet, we will get a dialog box of Protect Sheet where we need to set a Password of any digit.

For this purpose, first, select a cell or column which we need to protect, and then go to the Review tab and click on Protect Sheet.

Below is the screenshot of available data for locking the required column. And data available after it will be accessible for changes. So, to avoid this, we are going to lock that Product Type column so that no one will change it. Where we have observed that the Product Type column many times got changed by multiple logins. We got sample sales data from some products.

HOW TO LOCK INDIVIDUAL CELLS IN EXCEL 2013 DOWNLOAD

Sub ProtectCellsWithFormulas()įor Each rng In ActiveSheet.You can download this Lock Column Excel Template here – Lock Column Excel Template Example #1 Otherwise, the cell becomes or remains unlocked. If a cell has a formula, it locks the cell.

HOW TO LOCK INDIVIDUAL CELLS IN EXCEL 2013 CODE

The code example below checks every cell in the range ' B4:C9' from the active worksheet. Remember to save your file in either XLSM or XLAM format to save your VBA code. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Copy and paste the code into the module to run it. To run this code, you need to add a module into the workbook or the add-in file.

For Each rng In ActiveSheet.Range('B4:C9') A simple loop can be used to detect cells in a given range that contain formula. It returns a Boolean value based on whether the cell or range has a formula. The cells and ranges have a HasFormula property, which makes them read only. If you just need to lock only cells with formulas, you need to first identify cells that have formulas. You can use the Hidden status to hide your formulas as well. Set these two as True or False to change their status. Every cell and range can be made Locked and FormulaHidden properties. The second method is doing this via VBA code. Use the corresponding checkboxes to activate properties. Select a cell or a range of cells, and press Ctrl + 1 to open this menu and go to the Protection tab. The User Interface method requires using the Format Cells dialog. A cell can be marked as Locked, and/or Hidden, in two ways: Important note: Protecting a sheet does not lock individual cells! The cells you want to lock should be marked as Locked too. By setting a string for the parameter argument, you can lock your worksheets with a password. Both methods can get accept other optional arguments. These methods determine the protected status of a worksheet as the name suggests. Worksheets are objects in a workbook's worksheet collection and they have Protect and Unprotect methods.

HOW TO LOCK INDIVIDUAL CELLS IN EXCEL 2013 HOW TO

In this article, we're going to show you how to lock a cell in Excel formula using VBA. However, you can prevent users from going outside the intended boundaries by disabling certain sections of your workbook. Users entering data into the wrong cells or changing existing formulas can make data collection a tedious process.

How to lock individual cells in excel 2013