nomadhair.blogg.se

1. #Password protect cells in excel 2007 how to
2. #Password protect cells in excel 2007 code
3. #Password protect cells in excel 2007 password
4. #Password protect cells in excel 2007 download

#Password protect cells in excel 2007 download

To get the most out of this guide, download the exercise file here and follow along. All of our examples will be performed in Excel 2016 for Windows.

#Password protect cells in excel 2007 how to

In the following article, we will discuss just how to do this. What Excel actually allows us to do is to make our sheets or contents ‘read-only’ so that no changes can be saved to the protected parts of the file. In Excel 2016, we have options that allow us to protect our spreadsheet contents. These are just a couple of scenarios in which the ability to protect our sheets or even just specific cells within our sheet could be necessary. Sometimes the people we share our spreadsheet files may assume it is okay to make changes or perhaps they make changes unintentionally and then save the changes to the same file name.

Make sure to download the sample file attached to this tutorial so you can work with these examples in Excel.There may be times when we need to share an Excel file with others but we want to ensure that none of our data content gets altered in the process.

#Password protect cells in excel 2007 password

Once you get this form of the macro running well, then, add-in the other pieces that you want, like password protection. I didn't show you how to password protect the worksheet here because I wanted to focus on the method needed to protect only specific cells. This is a handy macro that allows you to have a lot of control over a worksheet and protecting the data within it.

#Password protect cells in excel 2007 code

Limit to a Column: If Target.Column = 1 And Target.Value "" ThenĬhange the 1 to whichever column you want the code to run on. Limit to a row: If Target.Row = 1 And Target.Value "" ThenĬhange the 1 to whichever row you want and the code will only run on that row. To limit the locking to a certain column or row, change the IF statement. You can lock the last edited cell regardless of which one it was by removing the IF statement in the macro and leaving it like this: Private Sub Worksheet_Change(ByVal Target As Range) To lock cell B5, it would be Range(B5).Locked = TrueĬontinue that pattern for whichever cell or range you want to lock. To lock another cell, just use the Range method of referencing cells. Target.Address is the cell that will be locked and this says to lock the cell that was just edited. Lock Cell Other Than the One that was EditedĬhange this line: Range(Target.Address).Locked = True You can also lock the cell only if a certain value was input by changing "" in the line above to = "desired value" and that allows you to do something like lock the cell only if OK was entered or something similar. You must put dollar signs in front of the column and row reference for this to work. To make the macro work on cell B1 or D15 or any cell, just replace $A$1 with the desired cell reference. This says that a cell should be locked only if cell A1 was edited and it is not empty. Looking at the macro above, this is the line that limits the macro to run only on specific cells: If Target.Address = "$A$1" And Target.Value "" Then I will show you how to edit this macro in the examples below. It checks which cell was edited and, if a certain cell was edited, it is then locked. This example is a generic example that is easy to modify. To do this, right-click the tab of the desired worksheet and click View Code and paste the macro into the window that opens.

This macro needs to be installed into the worksheet where it will run. It unprotects the worksheet, locks the cell, and then re-protects the sheet. The important code is this part: ActiveSheet.Protect Contents:=False If Target.Address = "$A$1" And Target.Value "" Then Macro to Auto-Lock Cells Private Sub Worksheet_Change(ByVal Target As Range) This needs to be done so that only specific cells that we choose will be locked from editing. Now, none of the cells in the worksheet are protected, which means that all of the cells in the worksheet will be editable when you go to protect the worksheet.