Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to protect formats in unprotected cells
Excel 2003, sp2
Hi I use unprotected cells in a worksheet for data entry. In some cases the cells are merged, and all cells have borders and cell colors. After protecting the worksheet (see code below) the user cannot change the unlocked cells formats. The menu command Edit-Delete-All is however still available to be used on the unlocked cells which results in all their formats being lost (including the merge). I there any way to prevent this from happening? Sub Lock_Sheet(ByRef wksSheetToLock As Excel.Worksheet) Select Case wksSheetToLock.CodeName Case "wksDataEntry" wksSheetToLock.Protect _ Password:=gsSHEET_PWD, _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True End Select End Sub Regards Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to protect formats in unprotected cells
I don't think so.
You could stop some of it--but I think the merged cells are going to be a problem. When you use Edit|Clear|All (not delete, right???), the range you just cleared takes the format from the Normal style. So you can modify the Normal style to be what you want. Changing the protection to unlocked so you don't change the cleared cells to locked is a common change. Format|style|Select Normal and click Modify to make your changes. But since styles live in workbooks, any workbook that needs this will have to have the same change made. You could change other attributes of the normal style, but that usually caused more trouble than it's worth--for example, changing number format changes dates, too. PO wrote: Excel 2003, sp2 Hi I use unprotected cells in a worksheet for data entry. In some cases the cells are merged, and all cells have borders and cell colors. After protecting the worksheet (see code below) the user cannot change the unlocked cells formats. The menu command Edit-Delete-All is however still available to be used on the unlocked cells which results in all their formats being lost (including the merge). I there any way to prevent this from happening? Sub Lock_Sheet(ByRef wksSheetToLock As Excel.Worksheet) Select Case wksSheetToLock.CodeName Case "wksDataEntry" wksSheetToLock.Protect _ Password:=gsSHEET_PWD, _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True End Select End Sub Regards Pete -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
edit a comment while protect sheet is on the cell is unprotected | Excel Worksheet Functions | |||
How change unprotected cell formats on protected worksheets? | Excel Programming | |||
Re-protect if originally protected, leave unprotected if not? | Excel Programming | |||
protect formats | Excel Programming | |||
put zero value to unprotected cells | Excel Programming |