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.
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.
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：