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 email@example.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.