lock cell based on a condition
 

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....

lock cell based on a condition
 

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

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...

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

To lock col H instead of D. There are two lines that use:
Target.Offset(0, 1)
instead use:
Target.Offset(0, 6)
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

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



--
Gary''s Student - gsnu200731


"Amanda" wrote:

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...

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

I get a syntax error when I run the first portion from my personal....

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200731


"Amanda" wrote:

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...

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

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.
--
Gary''s Student - gsnu200731


"Amanda" wrote:

I get a syntax error when I run the first portion from my personal....

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200731


"Amanda" wrote:

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...

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

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....



"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200731


"Amanda" wrote:

I get a syntax error when I run the first portion from my personal....

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200731


"Amanda" wrote:

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...

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200731


"Amanda" wrote:

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....

lock cell based on a condition
 

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

Is there any way I could mail it to you, so you could take a look?

"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

PUT NOTHING in personal.xls. Remove it if you put it there (Both parts).

Use only the application workbook.
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

When I look at the Visual Basic...this is what is showing...

Under my VBA Project (Recruitment Log.xls)
Microsoft Excel Object (folder)
Sheet 1(log)
This Workbook

Sheet 1's code is:
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


This worksheet's code is:

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, 6).Locked = True
Else
Target.Offset(0, 6).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


"Gary''s Student" wrote:

PUT NOTHING in personal.xls. Remove it if you put it there (Both parts).

Use only the application workbook.
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.

lock cell based on a condition
 

I see the problem.

1. remove the code from the ThisWorkbook area
2. remove the code from the worksheet area
3. leave the code in the standard module (set_up)

4. go back to the worksheet area and paste in our latest version:

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



Run setup before entering data in column A
--
Gary''s Student - gsnu200733


"Amanda" wrote:

When I look at the Visual Basic...this is what is showing...

Under my VBA Project (Recruitment Log.xls)
Microsoft Excel Object (folder)
Sheet 1(log)
This Workbook

Sheet 1's code is:
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


This worksheet's code is:

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, 6).Locked = True
Else
Target.Offset(0, 6).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


"Gary''s Student" wrote:

PUT NOTHING in personal.xls. Remove it if you put it there (Both parts).

Use only the application workbook.
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

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.

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"Amanda" wrote:

Yes that is fine....

Again, I appreciate all your help!

"Gary''s Student" wrote:

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??
--
Gary''s Student - gsnu200732


"Amanda" wrote:

Was it determined that this can't be done?

If so, thanks for all your help and attempts! You guys are awesome!

"Amanda" wrote:

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?

"Gary''s Student" wrote:

Do we need the Calculate event??
--
Gary''s Student - gsnu200731


"Gord Dibben" wrote:

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

On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student
wrote:

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.