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

[roybranagan]

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]

Roy,

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

Please Login or Register  to view this content.

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]

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]

Please Login or Register  to view this content.

[jomili]

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

Please Login or Register  to view this content.

[roybranagan]

Jomili and Nilem
Many thanks - that worked fine - I will continue with a few more tests and let you know tomorrow

[roybranagan]

Thanks guys
just what I needed