Locking cells

  • Thread starter Thread starter **{Steven}**
  • Start date Start date
Hi Steven
only with VBA (e.g. using the worksheet_change event. e.g. the
following would unlock cell A1 if the user enters a 1 in cell B1 (and
does lock cell A1 again after a different value is inserted in B1)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub
With Target
If .Value = 1 Then
Me.Unprotect
Me.Range("A1").Locked = False
Me.Protect
Else
Me.Unprotect
Me.Range("A1").Locked = True
Me.Protect
End If
End With
End Sub
 
Ok, the variable cell is AH14 and there are two cells that I want to lock or
unlock depending on whether there is a 1 or a 0 in AH14. The two cells I
want to lock and unlock are AC15 and AC16. How do I impliment that?

Steven
 
Hi Steven

try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("AH14")) Is Nothing Then Exit Sub
With Target
If .Value = 1 or .value = 0 Then
Me.Unprotect
Me.Range("AC15:AC16").Locked = False
Me.Protect
Else
Me.Unprotect
Me.Range("AC15:AC16").Locked = True
Me.Protect
End If
End With
End Sub
 
Hi Stephen

Slighlty different approach to Franks code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("AH14")) Is Nothing Then Exit Sub
Me.Protect UserInterfaceOnly:=True
Range("AC15:AC16").Locked = Not (Target.Value = 1 Or Target.Value = 0)
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
I guess I wasn't totally clear on my last question. I need to know how to
use that equation. Do I paste it into the cells that I want to lock and
unlock or what. How do I use it?

Steven
 
Frank, I appreciate your help so far. I tried the script you posted and it
did not lock the cells, they stayed unlocked no matter if there was a 1 or a
0 in AH14. I also got something about debugging and it highlighted the line
"Me.Range("AC15:AC16").Locked = False" in yellow. Do you know why this would
happen?

Steven
 
Hi Steven
where did you put the code: It has to go in your worksheet module!. Try
the following
- open your workbook and activate the sheet for which you want this
locking applied
- right-click on the tab-name
- in the context menu choose 'Code'
- Paste the code in the appearing VBA editor window (this is the
worksheet module)
- close the VBA editor
- save your workbook and try again
 
That's what I did when I got the debug dialog box and it did not lock or
unlock any cells when I changed AH14.

Steven
 
Hi Steven
have you protected your worksheet with a password? The macro assumes
that there is no password. another question:
whcih debug message di appear and in which line 8maybe there is a
linebreak due to the newsreader).
 
Yes, the sheet is protected, in order to keep certain parts from being
changed. If your email address is correct, I will send you a blank email, so
you can send me the code directly and by pass the newreader, if that is ok
with you.

Steven
 
Back
Top