Use macro commands to add passwords to Excel worksheet formulas

Many of the data in the worksheet are generated by formula calculations, so how can you let others see the results of a calculation and hide the formula you use? Today we are going to introduce you to use macros to solve this problem.

First, create macros

Start Excel, select Tools-Macros-record new macros, enter the name of the macro in the open window, and set the save in item to personal Macro Workbook, and click OK to enter the 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 and select "VBAProject" in the Open VB editing window (PERSONAL. XLS) "-Module"-"Module 1", and then replace the contents of the right window with the following:

Sub password protection ()//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 you do this, save the settings and exit the edit window. Of course, in your own Excel, or the default of 888888 into their own password, so as not to cause leakage.

Second, the application of macros

In Excel, open the Customize command on the Tools menu, and on the Commands tab, in categories, select Macros. Select Custom button in command and drag it onto 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, we need to encrypt the only click the button, you can protect the current worksheet, and the default password is 888888, how to do this is convenient enough.