Protecting cells & formatting

  • Thread starter Thread starter Guest
  • Start date Start date
xl2002 added the capability of formatting locked cells on a protected worksheet.

Before xl2002, you would need to provide a macro that would format the cell.

If you can live with only 3 formats (plus the default), then maybe
Format|conditional formatting would help.
 
It would depend on what kind of formatting you wanted to do.

If I wanted to allow the user to change the fill color to yellow, I could do
this:

I recorded a macro when I changed a cell to yellow and added a couple of
things--unprotecting the sheet and reprotecting it when I was done:

Option Explicit
Sub ColorOneCellYellow()

If Selection.Cells.Count > 1 Then
MsgBox "Please one cell at a time!"
Exit Sub
Else
ActiveSheet.Unprotect Password:="hi"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveSheet.Protect Password:="hi"
End If

End Sub

You may be able to protect the worksheet in code and even dump the .unprotect
and .protect statements. If you protect it nicely, your code can do more things
than the user can:

if the workbook isn't shared, this should work ok:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

(you could use the workbook_open even under ThisWorkbook, too.)

====

But when the workbook is shared, you can't change the worksheet protection--so
this code will fail.
 
Back
Top