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

  1. Johnf393
    August 4, 2014 at 7:16 am

    Muchos Gracias for your blog post. cgbgeckgkcce

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: