Protect cells with formula

[YOUNAN]

Hello

I need a VBA code to protect all cells containing formulas only and let me input data in cells not containing formula. the file has multiple sheets.
Of course I need a password and do I need to run this code every time I open the file or just run it once, and what if I delete the code from the file after running it, will the cells still be be locked ?

Best regards.

 

[YOUNAN]

Hi and thanks to every one helping me, below is a link to the file or project i am working on, the file is empty and no sensitive data in it, as i said before the original file will contain more sheets than the one submitted here, and i want the VBA code to search through the entire workbook and lock every cell that contains formula ( the final copy may contain more than 1300 sheets ) and leave the other cells open to enter data in it. After the code has locked the cells containing formula.

Is it possible to delete the code so that it wouldn't run every time i open the file ? maybe it will slow opening the file if it would check over 1300 sheets. cause when i an finished working with this file there will no new formulas added. and i will run this only once i am finished .

https://www.dropbox.com/s/ubbjk5zw5uhqtn3/Lock test copy of Church Test 2019 test.xlsm?dl=0

Thanks again and best regards.

 

[YOUNAN]

Hi again, any one here can help me please.

Best Regards.

 

[Rick Rothstein]

Hi and thanks to every one helping me, below is a link to the file or project i am working on, the file is empty and no sensitive data in it, as i said before the original file will contain more sheets than the one submitted here, and i want the VBA code to search through the entire workbook and lock every cell that contains formula ( the final copy may contain more than 1300 sheets ) and leave the other cells open to enter data in it. After the code has locked the cells containing formula.

Is it possible to delete the code so that it wouldn't run every time i open the file ? maybe it will slow opening the file if it would check over 1300 sheets. cause when i an finished working with this file there will no new formulas added. and i will run this only once i am finished.

The code I posted and which Dante improved upon does not go through the sheet locking cells; rather, all it does is see if the user selects a cell that has a formula and, if so, reacts to that selection. There is no impact from this during the opening of the workbook no matter how many sheets your workbook may have. Now, with that said, you are now saying you want the code to apply to ANY worksheet in the workbook, not just a few sheets. Below is the code that you would need to put into the ThisWorkbook code module (and you should delete any of the previous code you got from this forum, no matter where you installed it, before doing that)...

[table="width: 500"]
[tr]
	[td]Public PriorSelectedCell As Range

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Set PriorSelectedCell = ActiveCell
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo NoCellsFound
    If Not Intersect(Target, Sh.UsedRange.SpecialCells(xlCellTypeFormulas)) Is Nothing Then
        MsgBox "Your selection includes at least one cell that contains a formula which is not allowed!", vbExclamation
        PriorSelectedCell.Select
    End If
NoCellsFound:
    Set PriorSelectedCell = ActiveCell
    Application.EnableEvents = True
End Sub[/td]
[/tr]
[/table]