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