Hide Formulas in a Worksheet and Prevent Deletion

Add to Favorites

This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected like a regular protected worksheet in excel; this means that you can still add content to the workbook without a problem and you can edit everything as long as it is not a formula. You can enter a new formula but you cannot then delete that formula. In addition, if you select a cell with a formula, that cell's contents will not be displayed. This allows you to keep your formulas hidden from users and adds an extra level of security. If you want to edit a particular cell, you will have to click that cell and then "Unhide" that cell by typing a password (which is located in the VBA code). Also, if you decide to delete this macro from your workbook, you will have to re-enter the password to unprotect the workbook afterwards if you want everything to be unprotected.

Note: To change the password, locate where it says "password" in the macro vba code and change this word to any password you want. You will need to change this in three separate places within the macro.

Where to install the macro:  ThisWorkbook

Hide Formulas in a Worksheet and Prevent Deletion

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim formula As Range
On Error Resume Next
   Sh.Unprotect Password:="password"
   With Selection
   .Locked = False
   .FormulaHidden = False
End With
If Target.Cells.Count = 1 Then
If Target.HasFormula Then
   With Target
   .Locked = True
   .FormulaHidden = True
End With
   Sh.Protect Password:="password", UserInterFaceOnly:=True
End If
   ElseIf Target.Cells.Count > 1 Then
   Set formula = Selection.SpecialCells(xlCellTypeFormulas)
   If Not formula Is Nothing Then
   With Selection.SpecialCells(xlCellTypeFormulas)
   .Locked = True
   .FormulaHidden = True
End With
   Sh.Protect Password:="password", UserInterFaceOnly:=True
End If
End If
   On Error GoTo 0
End Sub





Similar Content on TeachExcel
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Delete All Comments in a Worksheet in Excel Macro
Macro: Excel macro that will delete all of the comment contained within the active or current wor...
Print All Charts That are in a Worksheet
Macro: This macro will print all of the embedded charts which are on the current active works...
List all Conditional Formatting Formulas in Excel
Tutorial: List all conditional formatting formulas in a worksheet in Excel. This allows you to quick...
Reverse Row or Column Order in a Worksheet
Macro: This macro reverses the order of a selected row or column in excel. It will completely rev...
Goal Seek in Excel
Tutorial: Find a desired result by having Excel automatically change input values in Excel. Using ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Delete All Comments in a Worksheet in Excel Macro
Macro: Excel macro that will delete all of the comment contained within the active or current wor...
Print All Charts That are in a Worksheet
Macro: This macro will print all of the embedded charts which are on the current active works...
Excel Forum