lock cell based on a condition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like a cell to be locked or unfillable based on the results of
another cell.

For example:
Column B has a formula that the results are "" or "x".
If possible, I would like to lock Colum C for those that return an "x" value.

Is this possible?
Thanks

PS I saw a bunch of messages about code, but I have no idea what that is....
 
We will enter and run two pieces of code:

1. put this in a standard module:

Sub set_up()
ActiveSheet.Unprotect
Cells.Locked = False
Cells.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

It leaves all cells unlocked and the sheet protected. Run this once before
editing


2. put this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B:B"), Target) Is Nothing Then
Exit Sub
End If

ActiveSheet.Unprotect
If Target.Value = "x" Then
Target.Offset(0, 1).Locked = True
Else
Target.Offset(0, 1).Locked = False
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True

ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

This code checks every entry made in column B. If it is x, then the
adjacent cell in column C is locked. If it is not x, then the adjacent cell
in column C is unlocked.

If you have never used VBA before, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Thanks...I'm trying to figure this out...

Question, I was actually simplifying the b & c column....the "x" is in
Column B, and the column I need locked is H.....what needs to change for that
to happen?

Thanks again for your help
 
One more question.....can I add this stuff to a worksheet I already have
formatted? I tried the first time and got an error when it ran...
 
To lock col H instead of D. There are two lines that use:
Target.Offset(0, 1)
instead use:
Target.Offset(0, 6)
 
If you already have a partially filled sheet, then:

1. enter set_up
2. go back to the sheet
3. run set_up from the sheet
4. get to the worksheet code area for that sheet
5. enter the sorksheet_change macro
6 go back to the sheet
7 try it out
 
I am pretty sure the problem is text wrapping in the post. For example:

AlllowFormattingRows is really one line with the one below it.

If you are still having trouble, tomorrow I will re-post a much more
"copy/paste"-able version.
 
I'm still having problems...I figured out the syntax error...and I ran the
set up....but when I put the worksheet_change under this worksheet...it does
not show up when I go to run it again....do I not have to?

It looks like the set up ran, because my sheet is protected....but all the
cells are unlocked and I can change info.....

Thank you for your patience....
 
Note that Amanda wants the trigger to be a calculated value of "x"

Operative word being "calculated" from formulas.

Change_Event won't do that.


Gord Dibben MS Excel MVP
 
If you know another way...I'm all ears....

Let me explain the form a little more.....
There are two types of #'s that will be input into column A: 07-XXX or E-XXX
(X being a number)....If the job begins with 07-XXX then I need column H open
for filling and column K locked.....if the job begins with E-XXX then I need
Column H blocked and column K open

I used the formula to give me an easy indicator for my conditional formula
to turn the columns that should be blocked dark gray.

Does that make sense?
 
Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!
 
It CAN be done.

Is it O.K. to check the first two characters in column A instead of the "x"
in column B to make the decision??
 
The setup reoutine is the same. Here is the new worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If


ActiveSheet.Unprotect
If Left(Target.Value, 1) = "E" Then
Target.Offset(0, 7).Locked = True
Target.Offset(0, 10).Locked = False
Else
Target.Offset(0, 7).Locked = False
Target.Offset(0, 10).Locked = True
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True


ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

The routine looks for entries in column A. If the entry starts with "E"
then H is locked and K is unlocked. If it does not start with W, visa-versa
 
Not sure what I am doing wrong, but when I go under tools, VBA, and paste the
below....if I try to run it, worksheet_change does not show up.
 
Remember that the change macro goes in a "special place", right from the
normal Excel window:


1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in: making sure there is only one copy of the code in
that window
4. close the VBE window
 
I have the first set_up unlock under personal.xls module.....then I placed
the second portion you just sent by right clicking on the tab and pasting it,
which placed it

When I try to type something in the "locked" cell, I get "user-defined type
not defined"

I also get this on any other excel spreadsheet I open, that I don't want
this on.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sorting columns when cell results based on other cells 1
Hide columns based on row values 2
Returning a value if multiple conditions are met 2
Excel VBA 1
Conditional Formatting based on a vertical Array. 2
Insert row with a locked cell 1
Complex conditional formatting 2
Cell Locking Question 2

Back
Top