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.

 

[JoeMo]

Once installed, this event code for Thisworkbook will run automatically whenever a worksheet in the workbook is automated.
To install ThisWorkbook code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

In the code below, replace the text in red font with your password.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Protect Password:="pswd", userinterfaceonly:=True
Sh.Cells.Locked = False
Sh.Cells.SpecialCells(xlCellTypeFormulas).Locked = True
End Sub

 

[YOUNAN]

Hi , i did exact as above, but i am able to delete formulas, for example i went in sheet one after doing all steps above saving the file , close it and reopening it.
anything else to do. Please advice.

 

[JoeMo]

Hi , i did exact as above, but i am able to delete formulas, for example i went in sheet one after doing all steps above saving the file , close it and reopening it.
anything else to do. Please advice.

Do you observe that the sheets are protected when you select them? If not, you may have inadvertently disabled events. Did you substitute your password?

 

[Rick Rothstein]

The following code (all to be installed in the worksheet's code module, the Public statement should be located at the top of the code window outside of any procedures) will not allow a user to select any range that includes a formula (the prior selection will be reactivated)... you do not have to activate Excel's worksheet protection unless you need it for other purposes.

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

Private Sub Worksheet_Activate()
  Set PriorSelectedCell = ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Cell As Range
  For Each Cell In Target
    If Left(Cell.Formula, 1) = "=" Then
      MsgBox "Your selection includes at least one cell that contains a formula which is not allowed!", vbExclamation
      PriorSelectedCell.Select
      Exit Sub
    End If
  Next
  Set PriorSelectedCell = Target
End Sub[/td]
[/tr]
[/table]

 

[YOUNAN]

Thanks for all whom replied above.

Here is what i do, I open the excel file, click on ALT+F11, double click on thisworkbook, enter the codes above, close the VBA windows, close the excel file, it asks to save, i save it with extension XLSM , reopen the file, still able to delete or change in cells containing formulas.

I tested both codes, but not working, am i missing something, is it possible to attach the file here ? so that you can check what is wrong, also i checked and nothing is protected in the file.

 

[Yongle]

I tested Rick's code
It works even on a EMPTY worksheet - I selected A1 and inserted a formula in that cell and moved to A2 - I was not able to select A1 again

I do not think you put the code in the SHEET module

Right click on sheet tab \ click on View Code \ paste code there

 

[YOUNAN]

Hi , in the following link are a picture some shows that Rick's code is in its place, as i said i did all as described above. but still not working.

https://wetransfer.com/downloads/f0...1f93583d2232f89c8d65ad2620190413220437/3a6b92

Please advice.

 

[Rick Rothstein]

Hi , in the following link are a picture some shows that Rick's code is in its place, as i said i did all as described above. but still not working.

https://wetransfer.com/downloads/f0...1f93583d2232f89c8d65ad2620190413220437/3a6b92

Please advice.

Sorry, I won't download from sites I am totally unfamiliar with, especially ones that want me to click a button allowing them to add stuff (cookies supposedly) to my system. Why don't you post to something safe and well-established like DropBox? Anyway, a picture showing my code installed in the right place tells us nothing useful. If my code is not working for you, then there is something about your setup that I have assumed incorrectly. The only way to resolve this is for you to post a copy of the workbook that my code does not work on to DropBox so that I (and others) can download it and watch directly how my code interacts with your data and its layout. Before you post that copy to DropBox, change any sensitive data to nonsense text (and verify my code still does not work after you make those changes).

 

[DanteAmor]

Thanks for all whom replied above.

Here is what i do, I open the excel file, click on ALT+F11, double click on thisworkbook, enter the codes above, close the VBA windows, close the excel file, it asks to save, i save it with extension XLSM , reopen the file, still able to delete or change in cells containing formulas.

I tested both codes, but not working, am i missing something, is it possible to attach the file here ? so that you can check what is wrong, also i checked and nothing is protected in the file.

You must double click on the sheet where you want the validation.
Or
Right click the tab of the sheet you want this to work, select view code & paste the code into the window that opens up.


Continuing with the idea of ​​@Rick Rothstein, but without loop.

Try this:

Public PriorSelectedCell As Range


Private Sub Worksheet_Activate()
  Set PriorSelectedCell = ActiveCell
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, UsedRange.SpecialCells(xlCellTypeFormulas, 23)) Is Nothing Then
        MsgBox "Your selection includes at least one cell that contains a formula which is not allowed!", vbExclamation
        PriorSelectedCell.Select
    End If
    Set PriorSelectedCell = [COLOR=#0000ff]ActiveCell[/COLOR]
    Application.EnableEvents = True
End Sub