Excel Trick Hide formula in sheet step by step VBA example

Excel Trick Hide formula in sheet step by step VBA example

Hide Formula

Sometime user may in need where he wants to hide the formula in formula bar. In this article I will take you through steps to make it happen.

Step 1: Select the range for which you don’t want to show formula. Follow below data where Column “E” “Total Earning” contains formula and “E2” cell showing formula in the formula bar as “=D2*C2“:

Step 2: Select E2:E11 or as per you wish and do one of the followings which will bring format dialog as shown in figure 1.3 below:

  1. press CTRL + 1 from the keyboard to launch the Format cells dialog
  2. Right click and select Format Cells… command from the popup (refer figure 1.1)
  3. Navigate to Home Tab and locate Format dropdown button under Cells group and Select Format Cells… command (refer figure 1.2)
figure 1.1
figure 1.2

Step 3: Select the Protection tab in the Format Cells dialog window and check Hidden checkbox as shown below and say OK:

Step 4: Select Review tab and Click on Protect Sheet button as shown below:

Step 5: Put the password under password text box as shown below and say OK:

Step 6: Reconfirm the password by supplying the same password supplied in Step 5 and say OK:

Result

VBA code example

Public Sub HideFormulaCodeExample()
    'Declare worksheet object
    Dim oSheet As Worksheet
    'Bind worksheet reference
    Set oSheet = ActiveSheet
    
    'Declare range object
    Dim oRange As Range
    'Bind selection to range
    Set oRange = Selection
    
    'Hide cells
    oRange.FormulaHidden = True
    
    'Protect sheet
    oSheet.Protect "12345"
    
    'Memory CleanUp
    Set oRange = Nothing
    Set oSheet = Nothing
End Sub

Next >> Function Wizard in Excel VBA code

Leave a Reply

Your email address will not be published.