Locking cell dependent on value of another cell

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Could someone please advise what the code would be to have the cell's
properties for Cells B20:G195 be changed to "locked" depending on whether
the corresponding cell/s on the same row/s in column A become/s true.
In Column A I have a formula that gives a value of true or false determined
by other factors.
Rob
 
One way:

Put this in your worksheet code module:

Private Sub Worksheet_Calculate()
Const PWORD As String = "drowssap"
Dim cell As Range
Me.Unprotect PWORD
For Each cell In Range("A20:A195")
With cell
.Offset(0, 1).Resize(1, 6).Locked = Not .Value
End With
Next cell
Me.Protect PWORD
End Sub
 
Thank you very much for that but it seems the cells are locked when the
value is false. They should lock when the value is true. How do I change
that? Is it with the word "not" after locked = ?
Rob
 
What happened when you changed this line:
..Offset(0, 1).Resize(1, 6).Locked = Not .Value
to
..Offset(0, 1).Resize(1, 6).Locked = .Value

(but you probably already tried it)
 
Thank you gentlemen.
Yes, that works great!!!
Just one other question if I may.
The default message comes up that the cell or chart is protected, etc.,
which is as it should be, but is there a way to have my own designed message
come up instead?
Rob
 
You can't change that. But you can make it so that the user can't even select a
protected cell--so they can't even try to change it.

Just an extra line at the bottom of the routine that J.E. gave you.

Option Explicit

Private Sub Worksheet_Calculate()
Const PWORD As String = "drowssap"
Dim cell As Range
Me.Unprotect PWORD
For Each cell In Range("A20:A195")
With cell
.Offset(0, 1).Resize(1, 6).Locked = .Value
End With
Next cell
Me.Protect PWORD
Me.EnableSelection = xlUnlockedCells

End Sub
 
Thanks Dave,
Except that when I pasted Option Explicit in last line (which I presume you
mean after
Me.EnableSelection = xlUnlockedCells and before End Sub), It comes up with
an error saying, "Compile error. Invalid inside procedure."
Yikes!??
Rob
 
Just to explain, the "option explicit" wasn't the change I was suggesting. (I
put that in almost all my posts so that any copying and pasting will put that in
their module. It just tells excel that you want it to check to see if you've
declared each variable (helps catch typos).

But the extra line I wanted you to add was this:
Me.EnableSelection = xlUnlockedCells

(I could have been a little more specific about which line was added.)
 
Thanks Dave,
Oh what a fool am I!! If I'd taken more time and checked it would have
been obvious. Sorry!
One other question. The procedure from J.E. works great together with your
add in but when I do any editing on another worksheet, it flashes between
that sheet and the sheet that holds the code, slowing down the procedure of
entering or editing data.
Even funnier, any other workbooks open also seems to flash back to that
workbook's sheet if the workbook with that code in it is also open. Should
it be amended somehow?
Rob
 
If you add two lines--one near the top and one near the bottom, it might hide
that flashing:

Option Explicit
Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False
Const PWORD As String = "drowssap"
Dim cell As Range
Me.Unprotect PWORD
For Each cell In Range("A20:A195")
With cell
.Offset(0, 1).Resize(1, 6).Locked = .Value
End With
Next cell
Me.Protect PWORD
Me.EnableSelection = xlUnlockedCells
Application.ScreenUpdating = True
End Sub

(The two lines that I added are the application.screenupdating = False & True
<vbg>.)

rob said:
Thanks Dave,
Oh what a fool am I!! If I'd taken more time and checked it would have
been obvious. Sorry!
One other question. The procedure from J.E. works great together with your
add in but when I do any editing on another worksheet, it flashes between
that sheet and the sheet that holds the code, slowing down the procedure of
entering or editing data.
Even funnier, any other workbooks open also seems to flash back to that
workbook's sheet if the workbook with that code in it is also open. Should
it be amended somehow?
Rob
Click to expand...
 
Terrific!
Thanks Dave. I had tried that before but only using the
"Application.ScreenUpdating = False" line, which didn't work at the time,
but now it does. Didn't realise I had to reset it to true.
BUT....
The problem still exists that when that file with this procedure is open,
any other file open at the same time seems to go to this procedure somehow
each time I edit any cell in the second file. It doesn't flash back and
forth any more, but there is definately a short time delay. (only a split
second, but I can't understand how the other file without the procedure
should be affected.

Rob
 
I could get xl to do that if I used a volatile function (=rand(), =indirect())
in a cell. It looks like excel will recalculate that worksheet whenever there's
a change to any worksheet.

I don't know a way around it, but xl2002 did crash when I was experimenting.
(Save your work often, just in case.)

In xl2002, you can turn off calculation at the worksheet level.

You could add a couple of lines to stop calculation when you leave the sheet
(and stay in the same workbook):

Under the sheet module:
Option Explicit
Private Sub Worksheet_Activate()
Me.EnableCalculation = True
End Sub
Private Sub Worksheet_Deactivate()
Me.EnableCalculation = False
End Sub


Under the ThisWorkbook module:
Option Explicit
Private Sub Workbook_Activate()
With ActiveSheet
If LCase(.Name) = "sheet1" Then
Worksheets(.Name).EnableCalculation = True
End If
End With
End Sub
Private Sub Worksheet_Deactivate()
Worksheets("sheet1").EnableCalculation = False
End Sub

This'll stop it when you change workbooks.

(I don't have a suggestion for versions below xl2002.)

You may find something at Charles Williams' site:
http://www.decisionmodels.com


rob said:
Terrific!
Thanks Dave. I had tried that before but only using the
"Application.ScreenUpdating = False" line, which didn't work at the time,
but now it does. Didn't realise I had to reset it to true.
BUT....
The problem still exists that when that file with this procedure is open,
any other file open at the same time seems to go to this procedure somehow
each time I edit any cell in the second file. It doesn't flash back and
forth any more, but there is definately a short time delay. (only a split
second, but I can't understand how the other file without the procedure
should be affected.

Rob
Click to expand...
 
Dave,
Thanks for your time and effort in trying to help me with this problem.
Unfortunately I only have Excel 2000 and your suggestion doesn't do anything
at all on that version, although if you had it on your system I'm sure you'd
find a cure.
No crashes though :)
One question about the code though,
with the lines....

If LCase(.Name) = "sheet1" Then
Worksheets(.Name).EnableCalculation = True

I presume I need to enter the name of the sheet within the quotes (which I
did) but what does the LCase do?
Rob
 
Yep to the "fix worksheet name" question. But again, this was added in xl2002,
so this won't work for you anyway.

I don't have a good answer for you.

rob said:
Dave,
Thanks for your time and effort in trying to help me with this problem.
Unfortunately I only have Excel 2000 and your suggestion doesn't do anything
at all on that version, although if you had it on your system I'm sure you'd
find a cure.
No crashes though :)
One question about the code though,
with the lines....

If LCase(.Name) = "sheet1" Then
Worksheets(.Name).EnableCalculation = True

I presume I need to enter the name of the sheet within the quotes (which I
did) but what does the LCase do?
Rob
Click to expand...
 
Back
Top