Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
Dear experts,
Is there any VB script of function in Excel to protect certain cells, but no need to protect the entire worksheet? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
On Feb 22, 7:48*am, YY san. wrote:
Dear experts, Is there any VB script of function in Excel to protect certain cells, but no need to protect the entire worksheet? Thanks, Unlock all the cells in the worksheet, then lock the ones you want protected, then protect the sheet, only the Locked cells are protected. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
No
Cell protection requires Worksheet Protection be enabled. Provide a description of what you would like to do. The usual method is to unprotect, do the deed then re-protect. Gord Dibben MS Excel MVP On Sun, 22 Feb 2009 06:48:06 -0800, YY san. wrote: Dear experts, Is there any VB script of function in Excel to protect certain cells, but no need to protect the entire worksheet? Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
You might be able to make use of this idea. Right click tab for the
worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window that appeared (see my additional comments after the code)... '*************** START OF CODE *************** Dim OldValue As Variant Private Const ProtectedAddresses As String = "A1,B2,C3,D4" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False MsgBox "The value in this cell cannot be changed!" Target.Value = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then OldValue = Target.Value End If End Sub '*************** END OF CODE *************** Change my example cell address (A1,B2,C3,D4) to the cell addresses of the cells you want to "protect" in the Private Const statement at the beginning of the code. Now, go back to the worksheet and try to change the values in those cells. -- Rick (MVP - Excel) "YY san." wrote in message ... Dear experts, Is there any VB script of function in Excel to protect certain cells, but no need to protect the entire worksheet? Thanks, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
I just keep on learning<g
Gord On Sun, 22 Feb 2009 13:34:46 -0500, "Rick Rothstein" wrote: You might be able to make use of this idea. Right click tab for the worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window that appeared (see my additional comments after the code)... '*************** START OF CODE *************** Dim OldValue As Variant Private Const ProtectedAddresses As String = "A1,B2,C3,D4" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False MsgBox "The value in this cell cannot be changed!" Target.Value = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then OldValue = Target.Value End If End Sub '*************** END OF CODE *************** Change my example cell address (A1,B2,C3,D4) to the cell addresses of the cells you want to "protect" in the Private Const statement at the beginning of the code. Now, go back to the worksheet and try to change the values in those cells. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
LOL... we all do Gord, not just you. I can't even begin to tell you how much
I have learned from you, as well as the others here, since I started volunteering in the Excel newsgroups. The newsgroups are an amazing resource for all... from novice users right up to the most experienced users. As for the code... using "global" variables to set up a cooperative exchange of information between event procedures... I got quite good at implementing this type of coding back when I volunteered in the compiled VB newsgroups. I ended up developing several solutions to "it can't be done" type problems back then using this technique... the technique can be quite powerful (when the situation is right for it). -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I just keep on learning<g Gord On Sun, 22 Feb 2009 13:34:46 -0500, "Rick Rothstein" wrote: You might be able to make use of this idea. Right click tab for the worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window that appeared (see my additional comments after the code)... '*************** START OF CODE *************** Dim OldValue As Variant Private Const ProtectedAddresses As String = "A1,B2,C3,D4" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False MsgBox "The value in this cell cannot be changed!" Target.Value = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then OldValue = Target.Value End If End Sub '*************** END OF CODE *************** Change my example cell address (A1,B2,C3,D4) to the cell addresses of the cells you want to "protect" in the Private Const statement at the beginning of the code. Now, go back to the worksheet and try to change the values in those cells. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
Hi Rick,
Thanks for your help, it is exactly what I wanted. By the way, can the cell ID in the code change to a range instead of individual cell ID? I have like A1 ~ A50. Thanks, To the rest of the folks, thanks for your responses too! Totally agreed, we are all still learning. I have got great tips and solution after I hooked onto this forum! "Rick Rothstein" wrote: LOL... we all do Gord, not just you. I can't even begin to tell you how much I have learned from you, as well as the others here, since I started volunteering in the Excel newsgroups. The newsgroups are an amazing resource for all... from novice users right up to the most experienced users. As for the code... using "global" variables to set up a cooperative exchange of information between event procedures... I got quite good at implementing this type of coding back when I volunteered in the compiled VB newsgroups. I ended up developing several solutions to "it can't be done" type problems back then using this technique... the technique can be quite powerful (when the situation is right for it). -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I just keep on learning<g Gord On Sun, 22 Feb 2009 13:34:46 -0500, "Rick Rothstein" wrote: You might be able to make use of this idea. Right click tab for the worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window that appeared (see my additional comments after the code)... '*************** START OF CODE *************** Dim OldValue As Variant Private Const ProtectedAddresses As String = "A1,B2,C3,D4" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False MsgBox "The value in this cell cannot be changed!" Target.Value = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then OldValue = Target.Value End If End Sub '*************** END OF CODE *************** Change my example cell address (A1,B2,C3,D4) to the cell addresses of the cells you want to "protect" in the Private Const statement at the beginning of the code. Now, go back to the worksheet and try to change the values in those cells. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
Yes... use a colon to join the top-left cell to the bottom-right cell. So,
for your example... Private Const ProtectedAddresses As String = "A1:A50" And, of course, these can be combined with other single cells and/or ranges. For example... Private Const ProtectedAddresses As String = "A1:A50,B2,C3:F6,G4" Any valid range designations (single cells or multi-cell ranges) can be combined using the comma to separate them. -- Rick (MVP - Excel) "YY san." wrote in message ... Hi Rick, Thanks for your help, it is exactly what I wanted. By the way, can the cell ID in the code change to a range instead of individual cell ID? I have like A1 ~ A50. Thanks, To the rest of the folks, thanks for your responses too! Totally agreed, we are all still learning. I have got great tips and solution after I hooked onto this forum! "Rick Rothstein" wrote: LOL... we all do Gord, not just you. I can't even begin to tell you how much I have learned from you, as well as the others here, since I started volunteering in the Excel newsgroups. The newsgroups are an amazing resource for all... from novice users right up to the most experienced users. As for the code... using "global" variables to set up a cooperative exchange of information between event procedures... I got quite good at implementing this type of coding back when I volunteered in the compiled VB newsgroups. I ended up developing several solutions to "it can't be done" type problems back then using this technique... the technique can be quite powerful (when the situation is right for it). -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I just keep on learning<g Gord On Sun, 22 Feb 2009 13:34:46 -0500, "Rick Rothstein" wrote: You might be able to make use of this idea. Right click tab for the worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window that appeared (see my additional comments after the code)... '*************** START OF CODE *************** Dim OldValue As Variant Private Const ProtectedAddresses As String = "A1,B2,C3,D4" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False MsgBox "The value in this cell cannot be changed!" Target.Value = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then OldValue = Target.Value End If End Sub '*************** END OF CODE *************** Change my example cell address (A1,B2,C3,D4) to the cell addresses of the cells you want to "protect" in the Private Const statement at the beginning of the code. Now, go back to the worksheet and try to change the values in those cells. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect cells without protecting the entire workbook.
Something else you may want to consider doing with my code. As posted, my
code will pop up the "The value in this cell cannot be changed!" message, but leave the active cell at the location it moved to when the user hit Enter or mouse clicked. Better (I think) would be to re-activate the cell the user attempted to change so the message that popped up references what the user is looking at when he/she dismisses the MessageBox. To accomplish this, just add this line of code... Target.Select immediately *after* this line of code... Target.Value = OldValue -- Rick (MVP - Excel) "YY san." wrote in message ... Hi Rick, Thanks for your help, it is exactly what I wanted. By the way, can the cell ID in the code change to a range instead of individual cell ID? I have like A1 ~ A50. Thanks, To the rest of the folks, thanks for your responses too! Totally agreed, we are all still learning. I have got great tips and solution after I hooked onto this forum! "Rick Rothstein" wrote: LOL... we all do Gord, not just you. I can't even begin to tell you how much I have learned from you, as well as the others here, since I started volunteering in the Excel newsgroups. The newsgroups are an amazing resource for all... from novice users right up to the most experienced users. As for the code... using "global" variables to set up a cooperative exchange of information between event procedures... I got quite good at implementing this type of coding back when I volunteered in the compiled VB newsgroups. I ended up developing several solutions to "it can't be done" type problems back then using this technique... the technique can be quite powerful (when the situation is right for it). -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I just keep on learning<g Gord On Sun, 22 Feb 2009 13:34:46 -0500, "Rick Rothstein" wrote: You might be able to make use of this idea. Right click tab for the worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window that appeared (see my additional comments after the code)... '*************** START OF CODE *************** Dim OldValue As Variant Private Const ProtectedAddresses As String = "A1,B2,C3,D4" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False MsgBox "The value in this cell cannot be changed!" Target.Value = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then OldValue = Target.Value End If End Sub '*************** END OF CODE *************** Change my example cell address (A1,B2,C3,D4) to the cell addresses of the cells you want to "protect" in the Private Const statement at the beginning of the code. Now, go back to the worksheet and try to change the values in those cells. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
You might be able to make use of this idea.
Hi Rick,
I also need to have this code to lock some cells. But do you know where on the code sheet to insert that code? Thanks On Sunday, February 22, 2009 9:48 AM YYsa wrote: Dear experts, Is there any VB script of function in Excel to protect certain cells, but no need to protect the entire worksheet? Thanks, On Sunday, February 22, 2009 12:55 PM Gord Dibben wrote: No Cell protection requires Worksheet Protection be enabled. Provide a description of what you would like to do. The usual method is to unprotect, do the deed then re-protect. Gord Dibben MS Excel MVP On Sun, 22 Feb 2009 06:48:06 -0800, YY san. wrote: On Sunday, February 22, 2009 1:34 PM Rick Rothstein wrote: You might be able to make use of this idea. Right click tab for the worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window that appeared (see my additional comments after the code)... '*************** START OF CODE *************** Dim OldValue As Variant Private Const ProtectedAddresses As String = "A1,B2,C3,D4" Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then On Error GoTo Whoops Application.EnableEvents = False MsgBox "The value in this cell cannot be changed!" Target.Value = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range(ProtectedAddresses)) Is Nothing Then OldValue = Target.Value End If End Sub '*************** END OF CODE *************** Change my example cell address (A1,B2,C3,D4) to the cell addresses of the cells you want to "protect" in the Private Const statement at the beginning of the code. Now, go back to the worksheet and try to change the values in those cells. -- Rick (MVP - Excel) "YY san." wrote in message ... On Sunday, February 22, 2009 1:46 PM Gord Dibben wrote: I just keep on learning<g Gord On Sunday, February 22, 2009 2:21 PM Rick Rothstein wrote: LOL... we all do Gord, not just you. I can't even begin to tell you how much I have learned from you, as well as the others here, since I started volunteering in the Excel newsgroups. The newsgroups are an amazing resource for all... from novice users right up to the most experienced users. As for the code... using "global" variables to set up a cooperative exchange of information between event procedures... I got quite good at implementing this type of coding back when I volunteered in the compiled VB newsgroups. I ended up developing several solutions to "it can't be done" type problems back then using this technique... the technique can be quite powerful (when the situation is right for it). -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... On Sunday, February 22, 2009 4:52 PM CurlyDave wrote: no Unlock all the cells in the worksheet, then lock the ones you want protected, then protect the sheet, only the Locked cells are protected. On Sunday, February 22, 2009 8:12 PM YYsa wrote: Hi Rick, Thanks for your help, it is exactly what I wanted. By the way, can the cell ID in the code change to a range instead of individual cell ID? I have like A1 ~ A50. Thanks, To the rest of the folks, thanks for your responses too! Totally agreed, we are all still learning. I have got great tips and solution after I hooked onto this forum! "Rick Rothstein" wrote: On Sunday, February 22, 2009 9:56 PM Rick Rothstein wrote: Yes... use a colon to join the top-left cell to the bottom-right cell. So, for your example... Private Const ProtectedAddresses As String = "A1:A50" And, of course, these can be combined with other single cells and/or ranges. For example... Private Const ProtectedAddresses As String = "A1:A50,B2,C3:F6,G4" Any valid range designations (single cells or multi-cell ranges) can be combined using the comma to separate them. -- Rick (MVP - Excel) "YY san." wrote in message ... On Monday, February 23, 2009 3:16 AM Rick Rothstein wrote: Something else you may want to consider doing with my code. As posted, my code will pop up the "The value in this cell cannot be changed!" message, but leave the active cell at the location it moved to when the user hit Enter or mouse clicked. Better (I think) would be to re-activate the cell the user attempted to change so the message that popped up references what the user is looking at when he/she dismisses the MessageBox. To accomplish this, just add this line of code... Target.Select immediately *after* this line of code... Target.Value = OldValue -- Rick (MVP - Excel) "YY san." wrote in message ... Submitted via EggHeadCafe SQL Server Table Valued Parameters / Types - Multiple Row Inserts http://www.eggheadcafe.com/tutorials...w-inserts.aspx |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
You might be able to make use of this idea.
".....Right click tab for the worksheet that has the cells you want to "protect" and select View Code from the popup menu that appears, then copy/paste the following code into the code window...." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Must I use IRM to protect an entire workbook? | Excel Discussion (Misc queries) | |||
need to lock 5 cells but not protect entire sheet | Excel Discussion (Misc queries) | |||
Protect selected cells without protecting worksheet | Excel Discussion (Misc queries) | |||
Protecting Entire Workbook from Deletion | Excel Discussion (Misc queries) | |||
Protecting an entire workbook | Excel Programming |