Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Hide Excel Formulas

Hide Formulas In Excel Without Sheet Protection

Got any Excel 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, SpecialCells Method and the UserInterFaceOnly Argument of the Protect 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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft