Protect cells with formula

YOUNAN

Board Regular
Joined
Feb 10, 2015
Messages
94
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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.
Rich (BB code):
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

Board Regular
Joined
Feb 10, 2015
Messages
94
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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.
Code:
[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

Board Regular
Joined
Feb 10, 2015
Messages
94
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,661
Office Version
365
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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/f05b27e482063176ec959936c377a69320190413220437/5cb7119f1f93583d2232f89c8d65ad2620190413220437/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).
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,816
Office Version
2007
Platform
Windows
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:
Code:
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
 

Forum statistics

Threads
1,081,973
Messages
5,362,508
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top