How to protect formats in unprotected cells

[PO]

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]

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.