Tuesday, June 3, 2014

Protect Formulas in Cells

To protect formulas in cells:
When we create a spreadsheet, most of us need to use formulas of some sort. Sometimes, however, you might not want other users to tamper/delete/overtype any formulas you included on your spreadsheet. The easy way of barring people from playing with your formulas is to protect your formulas with vba.
You can use the Worksheet Change function to protect the formulas in the cells.


Example code :    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$21" Then
Target.Formula = "=E21*F21"
ElseIf Target.Address = "$G$22" Then
Target.Formula = "=E22*F22"
ElseIf Target.Address = "$G$23" Then
Target.Formula = "=E23*F23"

End If
End Sub

or

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("K11")) Is Nothing Then
Target.Formula = "=G11*H11"
ElseIf Not Intersect(Target, Range("k12")) Is Nothing Then
Target.Formula = "=G12*H12"
ElseIf Not Intersect(Target, Range("k13")) Is Nothing Then
Target.Formula = "=G13*H13"
ElseIf Not Intersect(Target, Range("k14")) Is Nothing Then
Target.Formula = "=G14*H14"
ElseIf Not Intersect(Target, Range("k15")) Is Nothing Then
Target.Formula = "=G15*H15"
ElseIf Not Intersect(Target, Range("k16")) Is Nothing Then
Target.Formula = "=G16*H16"

End If
End Sub