Protect cells with formulas only - Excel 2007

B

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.
 
G

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
 
J

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
 
G

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
 
J

John F

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

Gord Dibben

Thanks for the feedback John

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

Gord
 
Top