MrExcel Publishing
Your One Stop for Excel Tips & Solutions

A workbook w/formulas need a CODE to find & Protect them


Posted by Tom Godwin on October 25, 2000 6:28 PM

I have a Workbook that has different type of info, from text, number, Formulas and so on.

What I am trying to do is write a code to just find the Formulas, so I can Protect

Those field and unprotect the field the users would use.

hmm dont know were to Start New to this
Thanks in Advance



Posted by Ivan Moala on October 26, 2000 2:17 AM

This routine will protect ALL formula cells and leave any text,constants unprotected.....note if
you have blank cells that you also want unprotected then you will need to change it.


Sub Protect_formulasOnly()
Dim Frm 'Formulas
Dim oConst 'text,constants ie work data
Dim All
Dim oCell

Set Frm = Range("A1").SpecialCells(xlCellTypeFormulas, 23)
Set oConst = Range("A1").SpecialCells(xlCellTypeConstants, 23)
Set All = Application.Union(Frm, oConst)

For Each oCell In All.Cells
If oCell.HasFormula Then
oCell.Locked = True
Else
oCell.Locked = False
End If
Next
ActiveSheet.Protect password:="test", userinterfaceonly:=True
End Sub

Ivan