Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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.... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condition based on cell colour | Excel Worksheet Functions | |||
Is there a way to delete a cell value based on a condition? | Excel Discussion (Misc queries) | |||
Fill a cell based on a condition being met | Excel Worksheet Functions | |||
Lock data in a cell a specific cell based on selection on other ce | Excel Worksheet Functions | |||
How to lock a row in a spreadsheet based on the value in a cell | Excel Worksheet Functions |