Sunday, February 24, 2013

How to Unlock a Cell in Excel If Information Is Entered into Another Cell


1. Open the Excel spreadsheet that you want to work with.
2. Select the cell that you want to allow someone to enter information into. Right-click the selected cell, and choose 'Format Cells.' Click the 'Protection' tab at the top of the window, and clear the 'Locked' check box. Click 'OK.'
3. Click the 'Review' tab at the top of the Excel window, and then click the 'Protect Sheet' button. Type in a password, if you need one, and click 'OK.' If you entered a password, you will have to type it in again. Be sure to write the password down so that you won't forget it. All of the cells except for the one you selected are now locked.
4. Press 'Alt' 'F11' to open the Visual Basic console. Select the sheet that you are working on from the list of worksheets.
5. Copy the following code into the open space on the right side of the Visual Basic window:Private Sub Worksheet_Change(ByVal Target As Range)Dim KeyCells As RangeSet KeyCells = Range('A1')If Not Application.Intersect(KeyCells, Range(Target.Address)) _Is Nothing ThenWith Worksheets('Sheet1').Unprotect Password:='qqq'.Range('B1').Locked = False.Protect Password:='qqq'End WithEnd IfEnd Sub
6. Edit the code for your specific worksheet. Change 'A1' to the cell that you want users to be able to alter. Change 'B1' to the cell you that want to automatically unlock when users input data into the first cell. Change 'Sheet1' to the name of your worksheet. Change 'qqq' to whatever password you are using to lock the cells. If you are not using a password, delete both instances of 'Password:='qqq'.
7. Press 'Alt' 'F11' to close the Visual Basic window. Your worksheet will now unlock the specified cell when a user enters information into the original cell. Save your work now, because when you test the worksheet out, the target cell will be unlocked.

Blogger news