How to Hide Formula in Excel Using VBA: 4 Methods

Method 1 – Embed VBA to Hide the Formula of a Range in Excel

Steps to Protect a Sheet:

• Go to the tab Review.
• Click Protect Sheet from the Protect group in the ribbon.

• A Protect Sheet pop-up box will come up. Provide any password to open the sheet in the Password to Unprotect sheet box.
• Click OK.

• Reenter the password to proceed again in the Confirm Password popup window.
• Click OK again.

You now have a password-protected Excel worksheet.

Now we will learn how to hide the formula for a range in that sheet with the VBA code.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer > Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

Sub HideFormulaInProtectedSheet()
    Worksheets("Hide Formula").Range("G11:J14").FormulaHidden = True
End Sub

Your code is now ready to run.

• Press F5 on your keyboard, or from the menu bar, select Run > Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the gif below for the result.

All the formulas behind the range G11:J14 are now hidden.

Worksheets("Hide Formula").Range("G11:J14").FormulaHidden = True

Method 2 – Apply VBA Macro to Hide Formula from Selection in Excel

Steps:

• Select the cell that you want to hide the formula.
• Then shown as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy and paste the following code in the code window.

Sub HiddenFormula()
    Selection.Locked = True
    Selection.FormulaHidden = True
    ActiveSheet.Protect Password:="ExcelDemy"
End Sub

Your code is now ready to run.

• Run the macro.
• Select the cell before going to the Visual Basic Editor; then you can select the cell this time as well. Just go back to the worksheet of interest and select the cell.
• Go to the Developer tab and click Macros.

• A Macro pop-up window will appear. Select the macro name from the Macro name list and press Run.

The formula behind the selected cell (Cell G11) is now hidden.

You can execute this code to hide formulas for multiple cells as well.

• JSelect multiple cells instead of selecting one single cell and Run the macro as we showed you above.

• The code will work just fine in hiding the formulas behind all those cells.

From the above gif, formulas from multiple cells, from G11 to J14, are now hidden after running the macro.

You must unprotect the sheet if you want to show the formulas again. How you can unprotect a sheet is shown below.

Steps to Unprotect a Sheet:

• Go to the tab Review.
• Click Unprotect Sheet from the Protect group in the ribbon.
• An Unprotect Sheet pop-up box will appear. Provide the password to open the sheet in the Password field. If you remember from the code, we stored “ExcelDemy” as our password. Insert that password to unprotect the sheet for the workbook provided in this article.
• Click OK.

Your sheet will become unprotected, and all the formulas will be visible again.

Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect Password:="ExcelDemy"

Method 3 – Implement Macro to Conceal the Formula in an Unprotected Sheet

Steps:

• Right-click on the sheet name and select View Code from the option list that appeared.

• Copy the following code and paste it into the code window of the specified worksheet.

Dim iDictionary As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iCell As Range
Dim iRange As Range
Set iRange = Range("G11:J14")
If iDictionary.Count <> iRange.Count Then
    For Each iCell In iRange
        iDictionary.Add iCell.Address, iCell.FormulaR1C1
    Next
End If
If (Target.Count = 1) And (Not Application.Intersect(iRange, Target) Is Nothing) And (Target.HasFormula) Then
    With Target
        .Value = .Value
    End With
Else
    For Each iCell In iRange
        iCell.formula = iDictionary.Item(iCell.Address)
    Next
End If
End Sub

• Run this code, save it.
• Click on the Tools > References… from the code window tab.

• From References – VBAProject pop-up window, check Microsoft Scripting Runtime.
• Click OK.

• Go back to the worksheet of interest and click on the cells to check whether the formulas behind them are hidden now or not.

See from the above gif, if you click on the cells that hold formulas behind the results, you will see that the formulas are hidden now.

Dim iCell As Range
Dim iRange As Range

Set iRange = Range("G11:J14")

If iDictionary.Count <> iRange.Count Then
    For Each iCell In iRange
        iDictionary.Add iCell.Address, iCell.FormulaR1C1
    Next
End If

If (Target.Count = 1) And (Not Application.Intersect(iRange, Target) Is Nothing) And (Target.HasFormula) Then
    With Target
        .Value = .Value
    End With

Else
    For Each iCell In iRange
        iCell.formula = iDictionary.Item(iCell.Address)
    Next
End If

Method 4 – Embed VBA to Hide the Formula and Prevent the Cells from Deleting

Steps:

• Go to the code window from the View Code option that appears by right-clicking the worksheet of interest.
• Copy the following code and paste it into the code window of the specified worksheet.

Private Sub Workbook_SheetSelectionChange(ByVal iObj As Object, ByVal Target As Range)
Dim iRange As Range
On Error Resume Next
   iObj.Unprotect Password:="ExcelDemy"
   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
   iObj.Protect Password:="ExcelDemy", UserInterFaceOnly:=True
End If
   ElseIf Target.Cells.Count > 1 Then
   Set iRange = Selection.SpecialCells(xlCellTypeFormulas)
   If Not iRange Is Nothing Then
   With Selection.SpecialCells(xlCellTypeFormulas)
   .Locked = True
   .FormulaHidden = True
End With
   iObj.Protect Password:="ExcelDemy", UserInterFaceOnly:=True
End If
End If
   On Error GoTo 0
End Sub

• Don’t run this code; save it.
• Go back to the worksheet of interest and if you click on any cell then the cell will show you no formula, meaning the formula is hidden.

If you right-click on any cell, you will notice the Delete option is unavailable, meaning you can’t delete the cell now.

Dim iRange As Range

On Error Resume Next

   iObj.Unprotect Password:="ExcelDemy"
   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

iObj.Protect Password:="ExcelDemy", UserInterFaceOnly:=True
End If

ElseIf Target.Cells.Count > 1 Then
   Set iRange = Selection.SpecialCells(xlCellTypeFormulas)

If Not iRange Is Nothing Then
   With Selection.SpecialCells(xlCellTypeFormulas)
   .Locked = True
   .FormulaHidden = True
End With

Download Workbook

You can download the free practice Excel workbook from here.

Related Articles

• How to Lock and Unlock Cells in Excel Using VBA
• Excel VBA to Lock Cells without Protecting Sheet
• Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking
• How to Protect Specific Columns Using VBA in Excel

<< Go Back to Hide Excel Formulas | Excel Formulas | Learn Excel