Protect cells with formulas only - Excel 2007

  • Thread starter Thread starter John F
  • Start date Start date
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.
 
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
 
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
 
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
 
Excellent. Short term challenge solved and something to work on for the
future .. an ideal solution. Many thanks.
 
Thanks for the feedback John

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

Gord
 
Back
Top