In this article, we will use change event of worksheet to protect cells which contain formulas.
Raw data consists of employee sales data, which includes bifurication by month and year.
We want to find out total sales by month, as well as by year.
We have used sum function to calculate total sales on monthly and yearly basis. Change event of worksheet is used to protect cells which contain formulas. When we try to edit a cell containing a formula, we will get a pop-up message, saying that the cell value can’t be changed.
HasFormula method will return True if a cell contains a formula. Otherwise it will return False.
The above code is used to protect a cell.
Note: A cell will be protected only if it is locked. So before protecting a cell, it must be locked.
The above code is used to unprotect a cell.
Please follow below for the code
'Add below code in worksheet module Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range For Each rng In Target.Cells If rng.HasFormula Then ActiveSheet.Protect Else ActiveSheet.Unprotect End If Next rng End Sub
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org