Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
Hi Programmers,
I've realized that I need to use data validation to protect formulas in my worksheets. However, due to the enormity of the workbook, this would take about the rest of the summer. Also, I don't like to use the basic cell protection feature because of Excel's pain-in-the-rear protection popups. I am using Excel 2002. A thought occurred to me-- I've colored all cells that users should not alter with the color gray. So, is there a way for code to detect this and then not allow users to alter the contects of the gray-colored cells? Thanks so much |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
Give this VB idea a try. Right click the tab at the bottom of your worksheet
and select View Code from the popup window, then copy/paste the following code into the code window that opened up, then go back to the worksheet and try to change the contents of one or your gray cells. Be sure to read the notes listed after the code. '*************** START OF CODE *************** Dim OldValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then On Error GoTo Whoops Application.EnableEvents = False ' MsgBox "Gray cells cannot be changed!" Target.Formula = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then OldValue = Target.Formula End Sub '*************** END OF CODE *************** Note 1: I assumed your gray color was the one with a ColorIndex of 15 (adjust the value in the If..Then statement accordingly). Note 2: I included (but commented out) a MessageBox that could be displayed to the user if you want. Note 3: Be aware that a user can still change a cell by changing its color from gray to any other color or to no color. -- Rick (MVP - Excel) "Nick" wrote in message ... Hi Programmers, I've realized that I need to use data validation to protect formulas in my worksheets. However, due to the enormity of the workbook, this would take about the rest of the summer. Also, I don't like to use the basic cell protection feature because of Excel's pain-in-the-rear protection popups. I am using Excel 2002. A thought occurred to me-- I've colored all cells that users should not alter with the color gray. So, is there a way for code to detect this and then not allow users to alter the contects of the gray-colored cells? Thanks so much |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
try this
Option Explicit Const cGREY As Long = 12632256 Sub LockSheet() Dim cell As Range For Each cell In ActiveSheet.UsedRange.Cells If cell.Interior.Color = cGREY Then cell.Locked = False cell.FormulaHidden = False End If Next ActiveWorkbook.Protect Structu=True, Windows:=False ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Nick" wrote in message ... Hi Programmers, I've realized that I need to use data validation to protect formulas in my worksheets. However, due to the enormity of the workbook, this would take about the rest of the summer. Also, I don't like to use the basic cell protection feature because of Excel's pain-in-the-rear protection popups. I am using Excel 2002. A thought occurred to me-- I've colored all cells that users should not alter with the color gray. So, is there a way for code to detect this and then not allow users to alter the contects of the gray-colored cells? Thanks so much |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
Thanks Guys,
I will try these solutions by tomorrow... If this works, I'll need to do the same with another color, light orange, I think color index 40. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
I'm thinking this version of my code would be safer... it prevents the user
from being able to select a range that includes a gray cell (which, if they could do, would allow them to delete the contents of every selected cell even if it were gray... this code prevents that)... '*************** START OF CODE *************** Dim OldValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then On Error GoTo Whoops Application.EnableEvents = False ' MsgBox "Gray cells cannot be changed!" Target.Formula = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim C As Range For Each C In Target If C.Interior.ColorIndex = 15 Then OldValue = C.Formula C.Select Exit For End If Next End Sub '*************** END OF CODE *************** -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this VB idea a try. Right click the tab at the bottom of your worksheet and select View Code from the popup window, then copy/paste the following code into the code window that opened up, then go back to the worksheet and try to change the contents of one or your gray cells. Be sure to read the notes listed after the code. '*************** START OF CODE *************** Dim OldValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then On Error GoTo Whoops Application.EnableEvents = False ' MsgBox "Gray cells cannot be changed!" Target.Formula = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then OldValue = Target.Formula End Sub '*************** END OF CODE *************** Note 1: I assumed your gray color was the one with a ColorIndex of 15 (adjust the value in the If..Then statement accordingly). Note 2: I included (but commented out) a MessageBox that could be displayed to the user if you want. Note 3: Be aware that a user can still change a cell by changing its color from gray to any other color or to no color. -- Rick (MVP - Excel) "Nick" wrote in message ... Hi Programmers, I've realized that I need to use data validation to protect formulas in my worksheets. However, due to the enormity of the workbook, this would take about the rest of the summer. Also, I don't like to use the basic cell protection feature because of Excel's pain-in-the-rear protection popups. I am using Excel 2002. A thought occurred to me-- I've colored all cells that users should not alter with the color gray. So, is there a way for code to detect this and then not allow users to alter the contects of the gray-colored cells? Thanks so much |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
Last change... I promise! I didn't like which cell became active when you
tried to select a range that contained a gray cell, so I now reselect the cell the user was at prior to trying to select the range with a gray cell in it. '*************** START OF CODE *************** Dim OldValue As Variant Dim LastCell As Range Private Sub Worksheet_Activate() Set LastCell = ActiveCell End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then On Error GoTo Whoops Application.EnableEvents = False ' MsgBox "Gray cells cannot be changed!" Target.Formula = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim C As Range For Each C In Target If C.Interior.ColorIndex = 15 Then OldValue = C.Formula LastCell.Select Exit For End If Next Set LastCell = ActiveCell End Sub '*************** END OF CODE *************** -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I'm thinking this version of my code would be safer... it prevents the user from being able to select a range that includes a gray cell (which, if they could do, would allow them to delete the contents of every selected cell even if it were gray... this code prevents that)... '*************** START OF CODE *************** Dim OldValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then On Error GoTo Whoops Application.EnableEvents = False ' MsgBox "Gray cells cannot be changed!" Target.Formula = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim C As Range For Each C In Target If C.Interior.ColorIndex = 15 Then OldValue = C.Formula C.Select Exit For End If Next End Sub '*************** END OF CODE *************** -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this VB idea a try. Right click the tab at the bottom of your worksheet and select View Code from the popup window, then copy/paste the following code into the code window that opened up, then go back to the worksheet and try to change the contents of one or your gray cells. Be sure to read the notes listed after the code. '*************** START OF CODE *************** Dim OldValue As Variant Private Sub Worksheet_Change(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then On Error GoTo Whoops Application.EnableEvents = False ' MsgBox "Gray cells cannot be changed!" Target.Formula = OldValue End If Whoops: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 15 Then OldValue = Target.Formula End Sub '*************** END OF CODE *************** Note 1: I assumed your gray color was the one with a ColorIndex of 15 (adjust the value in the If..Then statement accordingly). Note 2: I included (but commented out) a MessageBox that could be displayed to the user if you want. Note 3: Be aware that a user can still change a cell by changing its color from gray to any other color or to no color. -- Rick (MVP - Excel) "Nick" wrote in message ... Hi Programmers, I've realized that I need to use data validation to protect formulas in my worksheets. However, due to the enormity of the workbook, this would take about the rest of the summer. Also, I don't like to use the basic cell protection feature because of Excel's pain-in-the-rear protection popups. I am using Excel 2002. A thought occurred to me-- I've colored all cells that users should not alter with the color gray. So, is there a way for code to detect this and then not allow users to alter the contects of the gray-colored cells? Thanks so much |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
Hi Again,
OK, I've tried Rick's, but couldn't get it to fire. I haven't tried Patrick's yet, but will. I initially wondered if I had the right color, which is Gray-25%. I then went to http://www.mvps.org/dmcritchie/excel/colors.htm. This webpage states, "Of the descriptive color names only those for index numbers 1 - 8 can be used in coding." Therefore, it appears that the long color name, 12632256, will need to be used. Nick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
First off, I just realized that the last change I made doesn't require all
the code I posted. This is all that is needed... it will prevent the user from being able to select a gray cell at all... '*************** START OF CODE *************** Dim LastCell As Range Private Sub Worksheet_Activate() Set LastCell = ActiveCell End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim C As Range For Each C In Target If C.Interior.ColorIndex = 15 Then LastCell.Select Exit For End If Next Set LastCell = ActiveCell End Sub '*************** END OF CODE *************** Now, as to the ColorIndex... if you don't know the ColorIndex value to use, note the address of one of your gray cell, let's say it is E2, then go into the VB editor and type this into the Immediate window... ? Range("E2").Interior.ColorIndex That will tell you the color index value to use in the If..Then statement in the code above. Alternately, if your cell is really assigned a Color value of 12632256 (as opposed to a ColorIndex value), you could try changing the If..Then statement as follows... If C.Interior.Color = 12632256 Then One of the above should make the code work for you. -- Rick (MVP - Excel) "Nick" wrote in message ... Hi Again, OK, I've tried Rick's, but couldn't get it to fire. I haven't tried Patrick's yet, but will. I initially wondered if I had the right color, which is Gray-25%. I then went to http://www.mvps.org/dmcritchie/excel/colors.htm. This webpage states, "Of the descriptive color names only those for index numbers 1 - 8 can be used in coding." Therefore, it appears that the long color name, 12632256, will need to be used. Nick |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible to Protect all Cells that are, say, Colored Gray?
One other reason why my code may not have worked for you... perhaps you
didn't put it in the location I said to put it in. If you had "macro" on your mind, you may have inadvertently put my code in a Module's code window rather than the worksheet's code window... my code is event code and *must* be located in the code window for the worksheet it is to apply to. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... First off, I just realized that the last change I made doesn't require all the code I posted. This is all that is needed... it will prevent the user from being able to select a gray cell at all... '*************** START OF CODE *************** Dim LastCell As Range Private Sub Worksheet_Activate() Set LastCell = ActiveCell End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim C As Range For Each C In Target If C.Interior.ColorIndex = 15 Then LastCell.Select Exit For End If Next Set LastCell = ActiveCell End Sub '*************** END OF CODE *************** Now, as to the ColorIndex... if you don't know the ColorIndex value to use, note the address of one of your gray cell, let's say it is E2, then go into the VB editor and type this into the Immediate window... ? Range("E2").Interior.ColorIndex That will tell you the color index value to use in the If..Then statement in the code above. Alternately, if your cell is really assigned a Color value of 12632256 (as opposed to a ColorIndex value), you could try changing the If..Then statement as follows... If C.Interior.Color = 12632256 Then One of the above should make the code work for you. -- Rick (MVP - Excel) "Nick" wrote in message ... Hi Again, OK, I've tried Rick's, but couldn't get it to fire. I haven't tried Patrick's yet, but will. I initially wondered if I had the right color, which is Gray-25%. I then went to http://www.mvps.org/dmcritchie/excel/colors.htm. This webpage states, "Of the descriptive color names only those for index numbers 1 - 8 can be used in coding." Therefore, it appears that the long color name, 12632256, will need to be used. Nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
colored cells | Excel Discussion (Misc queries) | |||
sum colored cells | Excel Discussion (Misc queries) | |||
Expanding Gray Bar That Covers Excel Cells | Excel Programming | |||
writing macro to gray out cells automatically | Excel Programming | |||
Cell right next to colored cells is automatically colored on entering a value | Excel Programming |