If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|