Protect formatting but not data from changes

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

Hello,

I have a user on Win 2000 using Excel 2000 SP3 and he
would like to lock the formatting of his worksheet, but
yet still allow the data to be changed. It seems as if
either you can protect it to not allow ANYTHING to be
changed or protect the cells from data changes only.

Any ideas? Thanks!
 
If you unlock the cell(s) (format|cells|Protection tab), you can protect the
worksheet (tools|protection|protect sheet) and I think you'll have what you
want.

Locking of cells and protecting of the worksheet go hand in glove. If you lock
a cell, then it really isn't locked until you protect the worksheet.
 
Rebecca,

You need to use VBA: run this macro with the sheet where you want to protect
the formatting active:

Sub ProtectFormat()
ActiveSheet.Protect UserInterfaceOnly:=True
End Sub

Copy the macro and paste it into a regular code module, then run it.

If you ever need to change the formatting, run this macro:

Sub UnProtectFormat()
ActiveSheet.Unprotect
End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top