Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I show protected cells in an EXCEL worksheet
Once I have protected a sheet leaving unprotected cell to allow users chsnge
or input data, users of this sheet do not know which cells they can change (unprotected) unless they try to click on every cell and are allowed to change the data in it. My question is: is there a way to display protected or unprotected sheet and if there is how to do it; and whether we can control the movement of the cursor in a protected sheet. |
#2
|
|||
|
|||
Try:
Sub ColorLocked() Dim cell As Range Dim ThisWs As Worksheet Set ThisWs = ActiveSheet ThisWs.Unprotect Password:="thisismypassword" For Each cell In ThisWs.UsedRange With cell If .Locked Then .Locked = False .Interior.ColorIndex = 6 .Locked = True End If End With Next ThisWs.Protect Password:="thisismypassword" End Sub --- To use, press ALT+F11, then go to Insert Module, and paste in the code above. From there press ALT+Q to close the VBE and run the macro from Tools Macro Macros. HTH Jason Atlanta, GA "laurentdada" wrote: Once I have protected a sheet leaving unprotected cell to allow users chsnge or input data, users of this sheet do not know which cells they can change (unprotected) unless they try to click on every cell and are allowed to change the data in it. My question is: is there a way to display protected or unprotected sheet and if there is how to do it; and whether we can control the movement of the cursor in a protected sheet. |
#3
|
|||
|
|||
This is a re-print of a previous post of mine that will highlight locked
cells Sub LockedCells() Dim cell As Range For Each cell In ActiveSheet.UsedRange If cell.Locked Then With cell With .Interior .ColorIndex = 35 .Pattern = xlSolid End With .BorderAround LineStyle:=xlDashDotDot, Weight:=xlThick, ColorIndex:=5 End With End If Next cell End Sub Change If cell.Locked Then to If Not cell.Locked Then -- HTH RP (remove nothere from the email address if mailing direct) "laurentdada" wrote in message ... Once I have protected a sheet leaving unprotected cell to allow users chsnge or input data, users of this sheet do not know which cells they can change (unprotected) unless they try to click on every cell and are allowed to change the data in it. My question is: is there a way to display protected or unprotected sheet and if there is how to do it; and whether we can control the movement of the cursor in a protected sheet. |
#4
|
|||
|
|||
Also, using the Tab key to navigate within the sheet will take the user to
only the unlocked cells. "laurentdada" wrote: Once I have protected a sheet leaving unprotected cell to allow users chsnge or input data, users of this sheet do not know which cells they can change (unprotected) unless they try to click on every cell and are allowed to change the data in it. My question is: is there a way to display protected or unprotected sheet and if there is how to do it; and whether we can control the movement of the cursor in a protected sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get excel to add cells by color? Like all green cells (s. | Excel Discussion (Misc queries) | |||
UDF and Calculation tree | Links and Linking in Excel | |||
How do I password protect cells in a spreadsheet created in Excel | Excel Worksheet Functions | |||
How do I password protect cells in a spreadsheet created in Excel | Excel Worksheet Functions | |||
stop excel from shifting cells up when a query returns no data | Excel Worksheet Functions |