I need a macro to lock only cells that a contain a formula

**roybranagan** · 01-17-2012, 05:06 AM

Many of my spreadsheets are very basic and are used by people who have no knowledge of spreadsheets (if I said "dont overtype a formula", they wouldnt know what I was talking about). I have tried putting comments that say "please dont type in this column/row/cell" to no avail.
I still get the spreadsheet back saying "its stopped working".

99% of the time it is because they have overtyped a formula.

Before I ship a worksheet to them I now lock and protect any ( and only) those cells that contain a formula by:

1) click on cell 0,0 to highlight all cells in the spreadsheet
2) click on format cells -> unlock cells
3) show all cells containing formulas by using keys cntrl + `
4) highlight all these cells
5) click on format cells -> lock cells
6) click on format cells -> protect sheet ( no password)

I would like to use a general macro to do the above for any worksheet. Any ideas on how to select and highlight only cells which contain a formula?

**jomili** · 01-17-2012, 09:33 AM

Roy,

I think if I get you started you can do this one yourself. This bit of code:

ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select

will select any cell on the activesheet containing a formula.

So, what I would do is turn on your macro recorder and run through your whole routine (as outlined earlier), knowing that parts 3 and 4 will be covered by the code I've provided. The recorded macro, with the code provided, should be the basis for your "general" macro. Let me know if more help is needed.

**roybranagan** · 01-18-2012, 05:17 AM

Jomili,
excellent suggestion - it seems to work for all but one test. When I have a sheet without any formula in a cell the macro fails with Run-time error '1004' no cells were found
Roy

**nilem** · 01-18-2012, 05:53 AM

On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select
On Error GoTo 0

**jomili** · 01-18-2012, 08:33 AM

Nilem showed the way. Try this as the full macro (untested):

Sub ProtectFormulas()
'---------------------------------------------------------------------------------------
' Procedure : ProtectFormulas
' Author    : Jomili
' Date      : 1/18/2012
' Purpose   : Unlocks non-formula cells, locks formula cells, protects sheet.
'---------------------------------------------------------------------------------------
    Cells.Locked = False
    On Error Resume Next
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
    On Error GoTo 0
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub