| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro To Lock Certain Cells... Please Help
Hi, Scenario: I am working on a sheet that has a column G and Colum H, Column contains validated list containing values "Yes" and "No", When i choos "Yes" from any cell in G column the value in the H column adjacent cel turns to "No", but if i choose "No" from column G the value in H colum turns "Yes". Problem: My problem is related to locking the cell that have a value of "No" means as soon as the value "No" gets into any cell in Column H th related cell should be locked for editing. Simply any cell in G or H i has "No" must be locked. else Open for editing. Hope Someone will Help, Regards, Tahi -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52365 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro To Lock Certain Cells... Please Help
tahir Wrote: Hi, Scenario: I am working on a sheet that has a column G and Colum H, Column contains validated list containing values "Yes" and "No", When i choos "Yes" from any cell in G column the value in the H column adjacent cel turns to "No", but if i choose "No" from column G the value in H colum turns "Yes". Problem: My problem is related to locking the cell that have a value of "No" means as soon as the value "No" gets into any cell in Column H th related cell should be locked for editing. Simply any cell in G or H i has "No" must be locked. else Open for editing. Currently i m using this code but it do not have locking or protectin the cells that contain "No" Value. ********Start of Code******** Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi Sub Application.EnableEvents = False If Target.Value = "Yes" Then Target.Offset(0, 1).Value = "No" Else If Target.Value = "No" Then Target.Offset(0, 1).Value = "Yes" Else Target.Offset(0, 1).Value = "" Application.EnableEvents = False If Target.Value = "New" Then Target.Offset(0, 1).Value = "Old" Else If Target.Value = "Old" Then Target.Offset(0, 1).Value = "New" Else Target.Offset(0, 1).Value = "" End If End If End If End If Application.EnableEvents = True End Sub ********End of Code******** Hope Someone will Help, Regards, Tahir Please Help Me on this , I m really stuck, This is my third day bu cant figure out how to lock the cells on selection of values from othe cell -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52365 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro To Lock Certain Cells... Please Help
tahir Wrote: Hi, Scenario: I am working on a sheet that has a column G and Colum H, Column contains validated list containing values "Yes" and "No", When i choos "Yes" from any cell in G column the value in the H column adjacent cel turns to "No", but if i choose "No" from column G the value in H colum turns "Yes". Problem: My problem is related to locking the cell that have a value of "No" means as soon as the value "No" gets into any cell in Column H th related cell should be locked for editing. Simply any cell in G or H i has "No" must be locked. else Open for editing. Currently i m using this code but it do not have locking or protectin the cells that contain "No" Value. ********Start of Code******** Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi Sub Application.EnableEvents = False If Target.Value = "Yes" Then Target.Offset(0, 1).Value = "No" Else If Target.Value = "No" Then Target.Offset(0, 1).Value = "Yes" Else Target.Offset(0, 1).Value = "" Application.EnableEvents = False If Target.Value = "New" Then Target.Offset(0, 1).Value = "Old" Else If Target.Value = "Old" Then Target.Offset(0, 1).Value = "New" Else Target.Offset(0, 1).Value = "" End If End If End If End If Application.EnableEvents = True End Sub ********End of Code******** Hope Someone will Help, Regards, Tahir Please Help Me on this , I m really stuck, This is my third day bu cant figure out how to lock the cells on selection of values from othe cell -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52365 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro To Lock Certain Cells... Please Help
tahir Wrote: Hi, Scenario: I am working on a sheet that has a column G and Colum H, Column contains validated list containing values "Yes" and "No", When i choos "Yes" from any cell in G column the value in the H column adjacent cel turns to "No", but if i choose "No" from column G the value in H colum turns "Yes". Problem: My problem is related to locking the cell that have a value of "No" means as soon as the value "No" gets into any cell in Column H th related cell should be locked for editing. Simply any cell in G or H i has "No" must be locked. else Open for editing. Currently i m using this code but it do not have locking or protectin the cells that contain "No" Value. ********Start of Code******** Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi Sub Application.EnableEvents = False If Target.Value = "Yes" Then Target.Offset(0, 1).Value = "No" Else If Target.Value = "No" Then Target.Offset(0, 1).Value = "Yes" Else Target.Offset(0, 1).Value = "" Application.EnableEvents = False If Target.Value = "New" Then Target.Offset(0, 1).Value = "Old" Else If Target.Value = "Old" Then Target.Offset(0, 1).Value = "New" Else Target.Offset(0, 1).Value = "" End If End If End If End If Application.EnableEvents = True End Sub ********End of Code******** Hope Someone will Help, Regards, Tahir Please Help Me on this , I m really stuck, This is my third day bu cant figure out how to lock the cells on selection of values from othe cell -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52365 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro To Lock Certain Cells... Please Help
tahir Wrote: Hi, Scenario: I am working on a sheet that has a column G and Colum H, Column contains validated list containing values "Yes" and "No", When i choos "Yes" from any cell in G column the value in the H column adjacent cel turns to "No", but if i choose "No" from column G the value in H colum turns "Yes". Problem: My problem is related to locking the cell that have a value of "No" means as soon as the value "No" gets into any cell in Column H th related cell should be locked for editing. Simply any cell in G or H i has "No" must be locked. else Open for editing. Currently i m using this code but it do not have locking or protectin the cells that contain "No" Value. ********Start of Code******** Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi Sub Application.EnableEvents = False If Target.Value = "Yes" Then Target.Offset(0, 1).Value = "No" Else If Target.Value = "No" Then Target.Offset(0, 1).Value = "Yes" Else Target.Offset(0, 1).Value = "" Application.EnableEvents = False If Target.Value = "New" Then Target.Offset(0, 1).Value = "Old" Else If Target.Value = "Old" Then Target.Offset(0, 1).Value = "New" Else Target.Offset(0, 1).Value = "" End If End If End If End If Application.EnableEvents = True End Sub ********End of Code******** Hope Someone will Help, Regards, Tahir Please Help Me on this , I m really stuck, This is my third day bu cant figure out how to lock the cells on selection of values from othe cell -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52365 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro To Lock Certain Cells... Please Help
Please Its Really Getting Hard for me, Please Hel -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52365 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro To Lock Certain Cells... Please Help
tahir Wrote: Hi, Scenario: I am working on a sheet that has a column G and Colum H, Column contains validated list containing values "Yes" and "No", When i choos "Yes" from any cell in G column the value in the H column adjacent cel turns to "No", but if i choose "No" from column G the value in H colum turns "Yes". Problem: My problem is related to locking the cell that have a value of "No" means as soon as the value "No" gets into any cell in Column H th related cell should be locked for editing. Simply any cell in G or H i has "No" must be locked. else Open for editing. Currently i m using this code but it do not have locking or protectin the cells that contain "No" Value. ********Start of Code******** Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi Sub Application.EnableEvents = False If Target.Value = "Yes" Then Target.Offset(0, 1).Value = "No" Else If Target.Value = "No" Then Target.Offset(0, 1).Value = "Yes" Else Target.Offset(0, 1).Value = "" Application.EnableEvents = False If Target.Value = "New" Then Target.Offset(0, 1).Value = "Old" Else If Target.Value = "Old" Then Target.Offset(0, 1).Value = "New" Else Target.Offset(0, 1).Value = "" End If End If End If End If Application.EnableEvents = True End Sub ********End of Code******** Hope Someone will Help, Regards, Tahir Please Help Me on this , I m really stuck, This is my third day bu cant figure out how to lock the cells on selection of values from othe cell -- tahi Student, working on a projec ----------------------------------------------------------------------- tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread.php?threadid=52365 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro to unlock and lock cells in password protected sheet | Excel Discussion (Misc queries) | |||
| Macro / Programming to lock cells | Excel Discussion (Misc queries) | |||
| lock cells | New Users to Excel | |||
| How do I lock all cells in a given row together to one another? | Excel Discussion (Misc queries) | |||
| how can I lock certain cells? | Excel Discussion (Misc queries) | |||
Hybrid Mode
