Protect cells with formulas only - Excel 2007

[John F]

What is the best way to only protect cells with formulas in them?

Many thanks

 

[Bob Sinclair]

Not sure if there is an easier way, but you can:

*Select the entire sheet (you can do this by clicking on the icon above row
1 and to the left of column A)
*Right click in the sheet and select FORMAT CELLS
*Go to the Protection tab and UNCHECK LOCKED
*Now, go back to your sheet, right click on the cell(s) you want to protect
and again select format cells
*Go to the protection tab and CHECK LOCKED
*When you are done selecting the cells to be locked go to the ribbon and
select the review tab
*Click on protect sheet
*Enter a password

This should result in a sheet containing selected cells that are password
protected.

 

[John F]

Thanks Bob. I was hoping that there might be an easier way.

 

[Gord Dibben]

You could make it a little easier.............

Sub lock_up()

With ActiveSheet
.Unprotect Password:="justme"
.Cells.Locked = False
.EnableSelection = xlUnlockedCells
.UsedRange.Select
End With
For Each cell In Selection
If cell.HasFormula Then
cell.Locked = True
Selection.FormulaHidden = False
End If
Next
Range("A1").Select
ActiveSheet.Protect Password:="justme"

End Sub


Gord Dibben MS Excel MVP

 

[John F]

Thanks Gord. I haven't done any VBA coding before but things all start
somewhere. Could you point me to a good starting point please.

Many thanks

 

[Gord Dibben]

John

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

I don't use 2007 yet but I'll give you the 2003 and earlier instructions.

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

NOTE: the code operates on whatever sheet is selected before running the macro.


Gord

 

[John F]

Excellent. Short term challenge solved and something to work on for the
future .. an ideal solution. Many thanks.

 

[Gord Dibben]

Thanks for the feedback John

Keep at that VBA.........opens a whole new Excel and other Office apps world.

Gord