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