Conditional Locking/Unlocking of cells
Posted by Leigh Hall on July 26, 2001 5:26 AM
I have a spreadsheet and I want to lock/unlock cells based on data keyed into an adjacent cell.
For example, if A1 contained text allow keying into B1. If A1 was blank lock B1 to prevent any data being entered.
Any help would be much appreciated.
Regards
Leigh
Posted by Bob on July 26, 2001 5:41 AM
OK, I haven't had my morning coffee yet, but one way you could do this is to start off by locking all of column B and protecting the worksheet. Then use the Worksheet_Change subroutine to unprotect the worksheet, unlock the cell (using the OFFSET function, then reprotect the worksheet. See Dax's answer to Andrew on 7/20 to get a handle on Worksheet_Change. If you need help on code to unprotect/reprotect post a follow up to this and I can dig some up.
Posted by Bob on July 26, 2001 5:47 AM
For some password code see Travis's response to Andre dated 7/25/01. Note that if you don't protect your macro code, the password is out there for all to see.
Posted by Leigh Hall on July 26, 2001 7:06 AM
Bob,
Thanksfor the response, any code you could supply would be appreciated.
Regards
Leigh
Posted by Bob on July 26, 2001 7:53 AM
This code should do it for you, but do read Dax's earlier message on Worksheet_Change - i found it useful. Also, you will need to lock the cells and protect the sheet while in Excel.
First you need to open Visual Basic, then choose View Project Explorer. You should see each worksheet on the left side of your screen. Select the sheet you are working on and RIGHT-CLICK on it and choose View Code. The window on the right will open. At the top will be two pulldowns. On the left pulldown choose Worksheet. On the right pulldown choose Change.
Add the following code to this screen. Don't duplicate the first and last lines.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count <> 1 Then Exit Sub
If they have selected more than one cell, get out.
If Target.Column = 1 Then
If they are changing a value in the first column then . . .
ActiveSheet.Unprotect Password:="YOUR-PASSWORD-HERE"
' Unprotect the worksheet.
Target.Offset(0, 1).Locked = False
Unlock the cell to the right of the active cell. In this case, column B.
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, Password:="YOUR-PASSWORD-HERE"
' Reprotect the sheet.
End If
End Sub
