Many data in the worksheet are generated by formula calculation, so how to let others only see the calculation result and hide the formula used? Today we will introduce the use of macros to solve this problem.

How to encrypt Excel table formula with macro function?

1. Create a macro

Start Excel, select “Tools” – “Macros” – “Record New Macro” in turn, enter the name of the macro in the opened window, and set the “Save in” item to “Personal Macro Workbook”, click “OK” button to enter macro recording mode. At this point, we can see the macro recording toolbar in the current window, click “Stop Recording” to exit the macro recording.

After exiting, press the “Alt+F11” key in Excel, select “VBAProject(PERSONAL.XLS)” – “Module” – “Module 1” in the opened VB editing window, and then replace the content in the right window to make:

Sub password protect() //name of macro

ActiveSheet.Unprotect (“888888”) //Default password

Cells.Select

Selection.Locked = False

Selection.SpecialCells(xlCellTypeFormulas, 23).Select

Selection.Locked = True

Selection.FormulaHidden = True

ActiveSheet.Protect(“123456”)

ActiveSheet.EnableSelection = xlUnlockedCells

End Sub

After finishing, save the settings and exit the editing window. The author hereby reminds everyone that in the actual operation process, you need to replace the default 888888 with your own password to avoid leakage.

How to encrypt Excel table formula with macro function?

2. Application macro

Open the “Custom” command in the “Tools” menu in Excel, select “Macro” in the “Category” of the “Command” tab, select “Custom Button” in the “Command”, and drag and drop it to On the toolbar, click the button on the toolbar, select the macro name we created in the pop-up window and save the settings.

At this point, when we want to encrypt, we only need to click this button to protect the current worksheet, and the default password is 888888. Is this operation convenient enough?

Recommended reading:

How to get rid of GB WhatsApp ban?

How is the recent social software Fouad WhatsApp different?

Leave a Reply

Your email address will not be published. Required fields are marked *