| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Locking cells
Is it possible to lock and unlock cells on one sheet based on certain
criteria in a certain cell on another sheet ? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
If you are prepared to use VBA
best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
I would like to try, with your assistance.
My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 , else unlock. This spreadsheet will be distributed to others. Will the VBA code transfer to other computers, with Excel, when sent by e-mail ? Thank You -- Duane Platt "Bernard Liengme" wrote: If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
This sub needs to be placed as a WorkSHEET module for Sheet5
I have comment out the protection steps - you can manually protect the work sheet as needed, or just remove the single quote that makes the VBA line a comment. Likewise add a single quote in front of Msgbox lines to make them comments (I use this to test my code) You may get an error the first time the code tries to lock the cells if they are already locked. It will work on other computers because the code is part of the workbook. People sometime have put code in a special file (PERSONAL.XLS) and that code will be seen only on the computer that is home to PERSONAL.XLS. You need to be aware what some people have Excel set in a way that prevents macros from running. You should warn the people who will get the file that you have a macro - hopefully they are under you in the organizational chart so you can 'boss' them! To learn more about VBA see David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then Worksheets("Sheet3").Range("B1:H10").Locked = True ' Worksheets("Sheet3").Protect MsgBox "locked" Else Worksheets("Sheet3").Range("B1:H10").Locked = False ' Worksheets("Sheet3").UnProtect MsgBox "unlocked" End If End If End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... I would like to try, with your assistance. My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 , else unlock. This spreadsheet will be distributed to others. Will the VBA code transfer to other computers, with Excel, when sent by e-mail ? Thank You -- Duane Platt "Bernard Liengme" wrote: If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
Bernard
Thank you again for your assistance. I may not know enough about macro's to make this work. I opened sheet5 - clicked on Tools/macro/VB Editor and typed in the code I didn't know what else to do so I opened sheet3 and tried the exercise. It did not work. I have double checked the code and it is entered as you wrote it. However, I found that by referencing cell G21 ( =sheet5!g21) in an obscure cell on sheet3 I can use Validation (custom) to solve my problem. Any thoughts on the VB solution will be appreciated though. Duane "Bernard Liengme" wrote: This sub needs to be placed as a WorkSHEET module for Sheet5 I have comment out the protection steps - you can manually protect the work sheet as needed, or just remove the single quote that makes the VBA line a comment. Likewise add a single quote in front of Msgbox lines to make them comments (I use this to test my code) You may get an error the first time the code tries to lock the cells if they are already locked. It will work on other computers because the code is part of the workbook. People sometime have put code in a special file (PERSONAL.XLS) and that code will be seen only on the computer that is home to PERSONAL.XLS. You need to be aware what some people have Excel set in a way that prevents macros from running. You should warn the people who will get the file that you have a macro - hopefully they are under you in the organizational chart so you can 'boss' them! To learn more about VBA see David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then Worksheets("Sheet3").Range("B1:H10").Locked = True ' Worksheets("Sheet3").Protect MsgBox "locked" Else Worksheets("Sheet3").Range("B1:H10").Locked = False ' Worksheets("Sheet3").UnProtect MsgBox "unlocked" End If End If End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... I would like to try, with your assistance. My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 , else unlock. This spreadsheet will be distributed to others. Will the VBA code transfer to other computers, with Excel, when sent by e-mail ? Thank You -- Duane Platt "Bernard Liengme" wrote: If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
The code must be on a worksheet module.
Right click the tab for the sheet 5; select View Code Do not type the sub, just copy and paste from my message (making sure there are no unneeded line breaks) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Bernard Thank you again for your assistance. I may not know enough about macro's to make this work. I opened sheet5 - clicked on Tools/macro/VB Editor and typed in the code I didn't know what else to do so I opened sheet3 and tried the exercise. It did not work. I have double checked the code and it is entered as you wrote it. However, I found that by referencing cell G21 ( =sheet5!g21) in an obscure cell on sheet3 I can use Validation (custom) to solve my problem. Any thoughts on the VB solution will be appreciated though. Duane "Bernard Liengme" wrote: This sub needs to be placed as a WorkSHEET module for Sheet5 I have comment out the protection steps - you can manually protect the work sheet as needed, or just remove the single quote that makes the VBA line a comment. Likewise add a single quote in front of Msgbox lines to make them comments (I use this to test my code) You may get an error the first time the code tries to lock the cells if they are already locked. It will work on other computers because the code is part of the workbook. People sometime have put code in a special file (PERSONAL.XLS) and that code will be seen only on the computer that is home to PERSONAL.XLS. You need to be aware what some people have Excel set in a way that prevents macros from running. You should warn the people who will get the file that you have a macro - hopefully they are under you in the organizational chart so you can 'boss' them! To learn more about VBA see David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then Worksheets("Sheet3").Range("B1:H10").Locked = True ' Worksheets("Sheet3").Protect MsgBox "locked" Else Worksheets("Sheet3").Range("B1:H10").Locked = False ' Worksheets("Sheet3").UnProtect MsgBox "unlocked" End If End If End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... I would like to try, with your assistance. My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 , else unlock. This spreadsheet will be distributed to others. Will the VBA code transfer to other computers, with Excel, when sent by e-mail ? Thank You -- Duane Platt "Bernard Liengme" wrote: If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
Bernard
I am making some headway I did as you suggested - Copy/paste I now get the Locked/unlocked Msxbox when date is changed. However, the range on sheet3 can still be written to. If I remove the single quote marks from Worksheets ("Sheet3").Protect and UnProtect it will work one time . It protects sheet3 entirely, not just the range. Then when I go back to change the date again, I get "Runtime Error 1004. Unable to set the Locked Property of the range class" Duane "Bernard Liengme" wrote: The code must be on a worksheet module. Right click the tab for the sheet 5; select View Code Do not type the sub, just copy and paste from my message (making sure there are no unneeded line breaks) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Bernard Thank you again for your assistance. I may not know enough about macro's to make this work. I opened sheet5 - clicked on Tools/macro/VB Editor and typed in the code I didn't know what else to do so I opened sheet3 and tried the exercise. It did not work. I have double checked the code and it is entered as you wrote it. However, I found that by referencing cell G21 ( =sheet5!g21) in an obscure cell on sheet3 I can use Validation (custom) to solve my problem. Any thoughts on the VB solution will be appreciated though. Duane "Bernard Liengme" wrote: This sub needs to be placed as a WorkSHEET module for Sheet5 I have comment out the protection steps - you can manually protect the work sheet as needed, or just remove the single quote that makes the VBA line a comment. Likewise add a single quote in front of Msgbox lines to make them comments (I use this to test my code) You may get an error the first time the code tries to lock the cells if they are already locked. It will work on other computers because the code is part of the workbook. People sometime have put code in a special file (PERSONAL.XLS) and that code will be seen only on the computer that is home to PERSONAL.XLS. You need to be aware what some people have Excel set in a way that prevents macros from running. You should warn the people who will get the file that you have a macro - hopefully they are under you in the organizational chart so you can 'boss' them! To learn more about VBA see David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then Worksheets("Sheet3").Range("B1:H10").Locked = True ' Worksheets("Sheet3").Protect MsgBox "locked" Else Worksheets("Sheet3").Range("B1:H10").Locked = False ' Worksheets("Sheet3").UnProtect MsgBox "unlocked" End If End If End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... I would like to try, with your assistance. My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 , else unlock. This spreadsheet will be distributed to others. Will the VBA code transfer to other computers, with Excel, when sent by e-mail ? Thank You -- Duane Platt "Bernard Liengme" wrote: If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
Try this revised code.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then With Worksheets("Sheet3") .Unprotect .Cells.Locked = False .Range("B1:H10").Locked = True .Protect End With MsgBox "locked" Else Worksheets("Sheet3").Unprotect MsgBox "unlocked" End If End If End Sub Gord Dibben MS Excel MVP On Fri, 30 Nov 2007 08:01:01 -0800, Duplatt wrote: Bernard I am making some headway I did as you suggested - Copy/paste I now get the Locked/unlocked Msxbox when date is changed. However, the range on sheet3 can still be written to. If I remove the single quote marks from Worksheets ("Sheet3").Protect and UnProtect it will work one time . It protects sheet3 entirely, not just the range. Then when I go back to change the date again, I get "Runtime Error 1004. Unable to set the Locked Property of the range class" Duane "Bernard Liengme" wrote: The code must be on a worksheet module. Right click the tab for the sheet 5; select View Code Do not type the sub, just copy and paste from my message (making sure there are no unneeded line breaks) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Bernard Thank you again for your assistance. I may not know enough about macro's to make this work. I opened sheet5 - clicked on Tools/macro/VB Editor and typed in the code I didn't know what else to do so I opened sheet3 and tried the exercise. It did not work. I have double checked the code and it is entered as you wrote it. However, I found that by referencing cell G21 ( =sheet5!g21) in an obscure cell on sheet3 I can use Validation (custom) to solve my problem. Any thoughts on the VB solution will be appreciated though. Duane "Bernard Liengme" wrote: This sub needs to be placed as a WorkSHEET module for Sheet5 I have comment out the protection steps - you can manually protect the work sheet as needed, or just remove the single quote that makes the VBA line a comment. Likewise add a single quote in front of Msgbox lines to make them comments (I use this to test my code) You may get an error the first time the code tries to lock the cells if they are already locked. It will work on other computers because the code is part of the workbook. People sometime have put code in a special file (PERSONAL.XLS) and that code will be seen only on the computer that is home to PERSONAL.XLS. You need to be aware what some people have Excel set in a way that prevents macros from running. You should warn the people who will get the file that you have a macro - hopefully they are under you in the organizational chart so you can 'boss' them! To learn more about VBA see David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then Worksheets("Sheet3").Range("B1:H10").Locked = True ' Worksheets("Sheet3").Protect MsgBox "locked" Else Worksheets("Sheet3").Range("B1:H10").Locked = False ' Worksheets("Sheet3").UnProtect MsgBox "unlocked" End If End If End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... I would like to try, with your assistance. My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 , else unlock. This spreadsheet will be distributed to others. Will the VBA code transfer to other computers, with Excel, when sent by e-mail ? Thank You -- Duane Platt "Bernard Liengme" wrote: If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Locking cells
Gord
That works -- Thank you This is a great site Merry Christmas to all Duane "Gord Dibben" wrote: Try this revised code. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then With Worksheets("Sheet3") .Unprotect .Cells.Locked = False .Range("B1:H10").Locked = True .Protect End With MsgBox "locked" Else Worksheets("Sheet3").Unprotect MsgBox "unlocked" End If End If End Sub Gord Dibben MS Excel MVP On Fri, 30 Nov 2007 08:01:01 -0800, Duplatt wrote: Bernard I am making some headway I did as you suggested - Copy/paste I now get the Locked/unlocked Msxbox when date is changed. However, the range on sheet3 can still be written to. If I remove the single quote marks from Worksheets ("Sheet3").Protect and UnProtect it will work one time . It protects sheet3 entirely, not just the range. Then when I go back to change the date again, I get "Runtime Error 1004. Unable to set the Locked Property of the range class" Duane "Bernard Liengme" wrote: The code must be on a worksheet module. Right click the tab for the sheet 5; select View Code Do not type the sub, just copy and paste from my message (making sure there are no unneeded line breaks) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Bernard Thank you again for your assistance. I may not know enough about macro's to make this work. I opened sheet5 - clicked on Tools/macro/VB Editor and typed in the code I didn't know what else to do so I opened sheet3 and tried the exercise. It did not work. I have double checked the code and it is entered as you wrote it. However, I found that by referencing cell G21 ( =sheet5!g21) in an obscure cell on sheet3 I can use Validation (custom) to solve my problem. Any thoughts on the VB solution will be appreciated though. Duane "Bernard Liengme" wrote: This sub needs to be placed as a WorkSHEET module for Sheet5 I have comment out the protection steps - you can manually protect the work sheet as needed, or just remove the single quote that makes the VBA line a comment. Likewise add a single quote in front of Msgbox lines to make them comments (I use this to test my code) You may get an error the first time the code tries to lock the cells if they are already locked. It will work on other computers because the code is part of the workbook. People sometime have put code in a special file (PERSONAL.XLS) and that code will be seen only on the computer that is home to PERSONAL.XLS. You need to be aware what some people have Excel set in a way that prevents macros from running. You should warn the people who will get the file that you have a macro - hopefully they are under you in the organizational chart so you can 'boss' them! To learn more about VBA see David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Here is my code: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("G21")) Is Nothing Then If Range("G21").Value < 2002 Then Worksheets("Sheet3").Range("B1:H10").Locked = True ' Worksheets("Sheet3").Protect MsgBox "locked" Else Worksheets("Sheet3").Range("B1:H10").Locked = False ' Worksheets("Sheet3").UnProtect MsgBox "unlocked" End If End If End Sub best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... I would like to try, with your assistance. My goal is to Lock cells $B$3:$AP$22 on Sheet3 if Sheet5!$G$21 < 2002 , else unlock. This spreadsheet will be distributed to others. Will the VBA code transfer to other computers, with Excel, when sent by e-mail ? Thank You -- Duane Platt "Bernard Liengme" wrote: If you are prepared to use VBA best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Duplatt" wrote in message ... Is it possible to lock and unlock cells on one sheet based on certain criteria in a certain cell on another sheet ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locking Cells | Excel Discussion (Misc queries) | |||
| Cells Locking? | Excel Discussion (Misc queries) | |||
| Locking Cells | Excel Discussion (Misc queries) | |||
| locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
| Locking Cells | Excel Discussion (Misc queries) | |||
Linear Mode
