| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
HI ALL,
' at start all cells in range c5:c1000 will be 0 ' wish to lock cell f5:f1000, when c5:c1000 is 1 ' wish if f5:f1000 access itself is not possible when c5:c1000 is 1 Private Sub Worksheet_Change(ByVal Target As Range) Set Target = Range("c5:c1000") If Target.cell(0, 3) = 1 Then Target.Cells.Offset(0, 3).Locked = True Else Target.Cells.Offset(0, 3).Locked = False End If End Sub PL HELP |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
Why don't you use datavalidation with the option set to custom. then you can put in any formula you want including references to other cells. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=173783 Microsoft Office Help |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
Put this macro in the sheet module. This macro will scan ech cell in range
C5:C1000 looking for values not equal to 1. If it finds a value other than 1 it unprotects the worksheet. In order to lock cells you have to highlight the cells you wish to lock, right click the range, then under the protection tab, click the Lock checkbox. The cells will not become locked untill you protect the sheet. This code protects the sheet and unprotects the sheets according to values in C5:C1000. So make sure range F5:F1000 is set to be locked when the sheet is protected. And unlock cells that you want the users to enter data in. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Sheet1") ' scan each cell in range to test if it = 1 For Each rng In .Range("C5:C1000") If rng.Value < 1 Then .Unprotect Exit Sub End If Next rng ' lock cells .Range("F5:F1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: HI ALL, ' at start all cells in range c5:c1000 will be 0 ' wish to lock cell f5:f1000, when c5:c1000 is 1 ' wish if f5:f1000 access itself is not possible when c5:c1000 is 1 Private Sub Worksheet_Change(ByVal Target As Range) Set Target = Range("c5:c1000") If Target.cell(0, 3) = 1 Then Target.Cells.Offset(0, 3).Locked = True Else Target.Cells.Offset(0, 3).Locked = False End If End Sub PL HELP |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
Dear Mr Ryan,
Your code has protected all cells i changed your code a little bit as below ' b5 is date, i want if b5 is in b7:b1000 then it must allow change in ' g7:j1000, else should not allow, as the dates could be prior to date in b5 ' which means i like to protect data entered prior to date in b5 ' suppose $b$5 has 27-01-2010 then when ' b12 has 26-01-2010 - dont allow to change in g12:j12 but ' b13 has 27-01-2010 - so allow change in g13:j13 Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Biz FT Daily") ' scan each cell in range to test if it = 1 For Each rng In .Range("B7:B1000") If rng.Value = Range("b5") Then ..Unprotect Else ..Protect Exit Sub End If Next rng ' lock cells .Range("G7:J1000").Locked = True ' protect sheet so Col. F is locked ..Protect End With End Sub pl help me out, thanks in advance. "Ryan H" wrote: Put this macro in the sheet module. This macro will scan ech cell in range C5:C1000 looking for values not equal to 1. If it finds a value other than 1 it unprotects the worksheet. In order to lock cells you have to highlight the cells you wish to lock, right click the range, then under the protection tab, click the Lock checkbox. The cells will not become locked untill you protect the sheet. This code protects the sheet and unprotects the sheets according to values in C5:C1000. So make sure range F5:F1000 is set to be locked when the sheet is protected. And unlock cells that you want the users to enter data in. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Sheet1") ' scan each cell in range to test if it = 1 For Each rng In .Range("C5:C1000") If rng.Value < 1 Then .Unprotect Exit Sub End If Next rng ' lock cells .Range("F5:F1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: HI ALL, ' at start all cells in range c5:c1000 will be 0 ' wish to lock cell f5:f1000, when c5:c1000 is 1 ' wish if f5:f1000 access itself is not possible when c5:c1000 is 1 Private Sub Worksheet_Change(ByVal Target As Range) Set Target = Range("c5:c1000") If Target.cell(0, 3) = 1 Then Target.Cells.Offset(0, 3).Locked = True Else Target.Cells.Offset(0, 3).Locked = False End If End Sub PL HELP |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
Yes, by default Excel has all cells set to be locked if the worksheet is
protected. Thats why I said you will have to unlock cells that users need to type in. There is no way to lock cells without protected the worksheet. My code will work for you with the criteria you asked for in your original post. Now you have totally new criteria. This new code below will work with the new criteria I think you gave. To explain. This code will test if the Target is within B7:B1000. If it is, the code will scan each cell in B7:B1000 testing if it is a date and if it is less than B5. If the cell is less that B5 then the adjacent cells in Col. G thru Col. J are locked else they are unlocked. For example, if B10 is less than B5 then G10:J10 is locked. Note make sure that the cells you want the user to type in are unlocked. I would suggest unlocking the whole sheet. Thus only Cols. G thru Cols. J will be locked. Hope this helps! If so, let me know, click "YES' below. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Dim rng As Range With Sheets("Biz FT Daily") ' test if Target is in Range B7:B1000 Set MyRange = Application.Intersect(.Range("B7:B1000"), Target) ' if Target is in range run loop If Not MyRange Is Nothing Then ' unprotect sheet so locked property can be changed .Unprotect ' scan each cell in range to test if it = 1 For Each rng In .Range("B7:B1000") ' test if rng value is a date If IsDate(rng.Value) Then ' lock or unlock cells dependent on B5 If rng.Value < .Range("B5") Then .Range(.Cells(rng.Row, "G"), .Cells(rng.Row, "J")).Locked = True Else .Range(.Cells(rng.Row, "G"), .Cells(rng.Row, "J")).Locked = False End If End If Next rng End If ' protect sheet so cells selected to be locked will lock .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: Dear Mr Ryan, Your code has protected all cells i changed your code a little bit as below ' b5 is date, i want if b5 is in b7:b1000 then it must allow change in ' g7:j1000, else should not allow, as the dates could be prior to date in b5 ' which means i like to protect data entered prior to date in b5 ' suppose $b$5 has 27-01-2010 then when ' b12 has 26-01-2010 - dont allow to change in g12:j12 but ' b13 has 27-01-2010 - so allow change in g13:j13 Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Biz FT Daily") ' scan each cell in range to test if it = 1 For Each rng In .Range("B7:B1000") If rng.Value = Range("b5") Then .Unprotect Else .Protect Exit Sub End If Next rng ' lock cells .Range("G7:J1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub pl help me out, thanks in advance. "Ryan H" wrote: Put this macro in the sheet module. This macro will scan ech cell in range C5:C1000 looking for values not equal to 1. If it finds a value other than 1 it unprotects the worksheet. In order to lock cells you have to highlight the cells you wish to lock, right click the range, then under the protection tab, click the Lock checkbox. The cells will not become locked untill you protect the sheet. This code protects the sheet and unprotects the sheets according to values in C5:C1000. So make sure range F5:F1000 is set to be locked when the sheet is protected. And unlock cells that you want the users to enter data in. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Sheet1") ' scan each cell in range to test if it = 1 For Each rng In .Range("C5:C1000") If rng.Value < 1 Then .Unprotect Exit Sub End If Next rng ' lock cells .Range("F5:F1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: HI ALL, ' at start all cells in range c5:c1000 will be 0 ' wish to lock cell f5:f1000, when c5:c1000 is 1 ' wish if f5:f1000 access itself is not possible when c5:c1000 is 1 Private Sub Worksheet_Change(ByVal Target As Range) Set Target = Range("c5:c1000") If Target.cell(0, 3) = 1 Then Target.Cells.Offset(0, 3).Locked = True Else Target.Cells.Offset(0, 3).Locked = False End If End Sub PL HELP |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
Dear Mr Ryan,
The post helped me and though i click "Yes" still i want to thank you very much for understanding my error in query and repling correctly. eddy stan "Ryan H" wrote: Yes, by default Excel has all cells set to be locked if the worksheet is protected. Thats why I said you will have to unlock cells that users need to type in. There is no way to lock cells without protected the worksheet. My code will work for you with the criteria you asked for in your original post. Now you have totally new criteria. This new code below will work with the new criteria I think you gave. To explain. This code will test if the Target is within B7:B1000. If it is, the code will scan each cell in B7:B1000 testing if it is a date and if it is less than B5. If the cell is less that B5 then the adjacent cells in Col. G thru Col. J are locked else they are unlocked. For example, if B10 is less than B5 then G10:J10 is locked. Note make sure that the cells you want the user to type in are unlocked. I would suggest unlocking the whole sheet. Thus only Cols. G thru Cols. J will be locked. Hope this helps! If so, let me know, click "YES' below. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Dim rng As Range With Sheets("Biz FT Daily") ' test if Target is in Range B7:B1000 Set MyRange = Application.Intersect(.Range("B7:B1000"), Target) ' if Target is in range run loop If Not MyRange Is Nothing Then ' unprotect sheet so locked property can be changed .Unprotect ' scan each cell in range to test if it = 1 For Each rng In .Range("B7:B1000") ' test if rng value is a date If IsDate(rng.Value) Then ' lock or unlock cells dependent on B5 If rng.Value < .Range("B5") Then .Range(.Cells(rng.Row, "G"), .Cells(rng.Row, "J")).Locked = True Else .Range(.Cells(rng.Row, "G"), .Cells(rng.Row, "J")).Locked = False End If End If Next rng End If ' protect sheet so cells selected to be locked will lock .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: Dear Mr Ryan, Your code has protected all cells i changed your code a little bit as below ' b5 is date, i want if b5 is in b7:b1000 then it must allow change in ' g7:j1000, else should not allow, as the dates could be prior to date in b5 ' which means i like to protect data entered prior to date in b5 ' suppose $b$5 has 27-01-2010 then when ' b12 has 26-01-2010 - dont allow to change in g12:j12 but ' b13 has 27-01-2010 - so allow change in g13:j13 Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Biz FT Daily") ' scan each cell in range to test if it = 1 For Each rng In .Range("B7:B1000") If rng.Value = Range("b5") Then .Unprotect Else .Protect Exit Sub End If Next rng ' lock cells .Range("G7:J1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub pl help me out, thanks in advance. "Ryan H" wrote: Put this macro in the sheet module. This macro will scan ech cell in range C5:C1000 looking for values not equal to 1. If it finds a value other than 1 it unprotects the worksheet. In order to lock cells you have to highlight the cells you wish to lock, right click the range, then under the protection tab, click the Lock checkbox. The cells will not become locked untill you protect the sheet. This code protects the sheet and unprotects the sheets according to values in C5:C1000. So make sure range F5:F1000 is set to be locked when the sheet is protected. And unlock cells that you want the users to enter data in. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Sheet1") ' scan each cell in range to test if it = 1 For Each rng In .Range("C5:C1000") If rng.Value < 1 Then .Unprotect Exit Sub End If Next rng ' lock cells .Range("F5:F1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: HI ALL, ' at start all cells in range c5:c1000 will be 0 ' wish to lock cell f5:f1000, when c5:c1000 is 1 ' wish if f5:f1000 access itself is not possible when c5:c1000 is 1 Private Sub Worksheet_Change(ByVal Target As Range) Set Target = Range("c5:c1000") If Target.cell(0, 3) = 1 Then Target.Cells.Offset(0, 3).Locked = True Else Target.Cells.Offset(0, 3).Locked = False End If End Sub PL HELP |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CAN WE LOCK CELLS AT F COLUMN BASED ON VALUE AT C COLUMN
No problem.
-- Cheers, Ryan "Eddy Stan" wrote: Dear Mr Ryan, The post helped me and though i click "Yes" still i want to thank you very much for understanding my error in query and repling correctly. eddy stan "Ryan H" wrote: Yes, by default Excel has all cells set to be locked if the worksheet is protected. Thats why I said you will have to unlock cells that users need to type in. There is no way to lock cells without protected the worksheet. My code will work for you with the criteria you asked for in your original post. Now you have totally new criteria. This new code below will work with the new criteria I think you gave. To explain. This code will test if the Target is within B7:B1000. If it is, the code will scan each cell in B7:B1000 testing if it is a date and if it is less than B5. If the cell is less that B5 then the adjacent cells in Col. G thru Col. J are locked else they are unlocked. For example, if B10 is less than B5 then G10:J10 is locked. Note make sure that the cells you want the user to type in are unlocked. I would suggest unlocking the whole sheet. Thus only Cols. G thru Cols. J will be locked. Hope this helps! If so, let me know, click "YES' below. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Dim rng As Range With Sheets("Biz FT Daily") ' test if Target is in Range B7:B1000 Set MyRange = Application.Intersect(.Range("B7:B1000"), Target) ' if Target is in range run loop If Not MyRange Is Nothing Then ' unprotect sheet so locked property can be changed .Unprotect ' scan each cell in range to test if it = 1 For Each rng In .Range("B7:B1000") ' test if rng value is a date If IsDate(rng.Value) Then ' lock or unlock cells dependent on B5 If rng.Value < .Range("B5") Then .Range(.Cells(rng.Row, "G"), .Cells(rng.Row, "J")).Locked = True Else .Range(.Cells(rng.Row, "G"), .Cells(rng.Row, "J")).Locked = False End If End If Next rng End If ' protect sheet so cells selected to be locked will lock .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: Dear Mr Ryan, Your code has protected all cells i changed your code a little bit as below ' b5 is date, i want if b5 is in b7:b1000 then it must allow change in ' g7:j1000, else should not allow, as the dates could be prior to date in b5 ' which means i like to protect data entered prior to date in b5 ' suppose $b$5 has 27-01-2010 then when ' b12 has 26-01-2010 - dont allow to change in g12:j12 but ' b13 has 27-01-2010 - so allow change in g13:j13 Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Biz FT Daily") ' scan each cell in range to test if it = 1 For Each rng In .Range("B7:B1000") If rng.Value = Range("b5") Then .Unprotect Else .Protect Exit Sub End If Next rng ' lock cells .Range("G7:J1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub pl help me out, thanks in advance. "Ryan H" wrote: Put this macro in the sheet module. This macro will scan ech cell in range C5:C1000 looking for values not equal to 1. If it finds a value other than 1 it unprotects the worksheet. In order to lock cells you have to highlight the cells you wish to lock, right click the range, then under the protection tab, click the Lock checkbox. The cells will not become locked untill you protect the sheet. This code protects the sheet and unprotects the sheets according to values in C5:C1000. So make sure range F5:F1000 is set to be locked when the sheet is protected. And unlock cells that you want the users to enter data in. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Sheets("Sheet1") ' scan each cell in range to test if it = 1 For Each rng In .Range("C5:C1000") If rng.Value < 1 Then .Unprotect Exit Sub End If Next rng ' lock cells .Range("F5:F1000").Locked = True ' protect sheet so Col. F is locked .Protect End With End Sub -- Cheers, Ryan "Eddy Stan" wrote: HI ALL, ' at start all cells in range c5:c1000 will be 0 ' wish to lock cell f5:f1000, when c5:c1000 is 1 ' wish if f5:f1000 access itself is not possible when c5:c1000 is 1 Private Sub Worksheet_Change(ByVal Target As Range) Set Target = Range("c5:c1000") If Target.cell(0, 3) = 1 Then Target.Cells.Offset(0, 3).Locked = True Else Target.Cells.Offset(0, 3).Locked = False End If End Sub PL HELP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| format cells in one column based on value in another column | New Users to Excel | |||
| Total cells of one column based on the values in another column? | New Users to Excel | |||
| Display cells(text) in one column based on cells which are present inother column | Excel Discussion (Misc queries) | |||
| Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
| macro to transpose cells in Column B based on unique values in Column A | Excel Programming | |||
Linear Mode
