Hide Excel Formulas
Category: [Excel] Demo Available
Hide Excel Formulas
Hide Formulas In Excel Without Sheet Protection
Got any Excel/VBA Questions? Excel Help
Stop Users Seeing Your Excel Formula
The normal method to stop formula viewing is to Hide Formulas via Format>Cells - Protection and check Hidden. Then apply Worksheet Protection. You would normally check the Locked box (checked by default) which would also prevent formula deletion.
TIP: Use F5 - Special to select specific cell types
The draw back with the standard method is that the entire Worksheet is protected and hence stopping many other features from being used when not even in a formula cell. The VBA method below, using Workbook_SheetSelectionChange, special cells method and the user intnerface only argument of the project method is a excellent work-around. Open the VBE (Alt+F11) then double click ThisWorkbook to access the private module of the Workbook Object.
What the code will do is Lock and Hide all Formula from viewing and protect the Worksheet by only locking and hiding anytime a Selection contains one or more Formula cells. Only the formula cells are Locked and Hidden. If there are no formulae cells in the selection, all cells within are unlocked and not hidden.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rFormulaCheck As Range
On Error Resume Next
Sh.Unprotect Password:="Secret"
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:="Secret", UserInterFaceOnly:=True
End If
ElseIf Target.Cells.Count > 1 Then
Set rFormulaCheck = Selection.SpecialCells(xlCellTypeFormulas)
If Not rFormulaCheck Is Nothing Then
With Selection.SpecialCells(xlCellTypeFormulas)
.Locked = True
.FormulaHidden = True
End With
Sh.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End If
On Error GoTo 0
End Sub
See also:
| Index to Excel VBA Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.