For confidentiality reasons in Excel, users are usually not expected to view and modify formulas. You can take advantage of Excel’s Lock, Hide, and Protect Worksheet features to hide and lock formulas. However, it will be more troublesome to set according to the conventional method, but using Excel’s macro, add a “protect formula” button on the toolbar. When you need to hide and lock the formula, you can complete the protection with a single click.
In order for this macro to work for all workbooks, it needs to be saved to a “Personal Macro Workbook”.
Step 1: Execute the “Tools→Macro→Record New Macro” command to open the “Record New Macro” dialog box.
Step 2: Click the “Save in” drop-down button on the right, and in the drop-down list that appears, select the “Personal Macro Workbook” option to enter the recording state.
Step 3: Click the “Stop Recording” button on the “Stop Recording” toolbar to exit the recording state.
Note: The previous operation is to add a “personal macro workbook” in Excel.
Step 4: Press the Alt+F11 key combination to enter the VBA editing state, expand the “VBAProject (PERSONAL.XLS)” and “Module” options on the left side in turn, and double-click the “Module 1” below.
Step 5: Replace the code in the edit area on the right with the following code:
Sub protect formula()
ActiveSheet.Unprotect (“123456”) ‘Unprotect the worksheet
Cells.Select ‘Select the entire table
Selection.Locked = False ‘Unlock
Selection.SpecialCells(xlCellTypeFormulas, 23).Select ‘Select the cell containing the formula
Selection.Locked = True ‘Add locked
Selection.FormulaHidden = True ‘Add hidden
ActiveSheet.Protect (“123456”) ‘Protect the sheet and set a password
ActiveSheet.EnableSelection = xlUnlockedCells ‘Make locked cells unselectable
‘The following code is used to save the file
If Left(ActiveWorkbook.Name, 4) = “Book” Then
y = InputBox(“Please enter the save file name:”)
z = InputBox(“Please enter the path to save the file:”)
ChDir z
ActiveWorkbook.SaveAs Filename:=z & “\” & y & “.xls”
Else
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & “\” & ActiveWorkbook.Name
End If
End Sub
After the input is complete, close the VBA editing window and return.
hint
★In the above code, “Protection formula” is the macro name, which can be replaced with other names during actual input.
★In the code, the English single quotation mark and the following characters are the explanation of the code, and it is not necessary to input it in actual input.
★Please modify the password (“123456”) in the code according to actual needs (it needs to be consistent before and after).
add button
Add a macro button on the toolbar, which is convenient to call at any time.
Step 1: Execute the “Tools → Customize” command to open the “Customize” dialog box
Step 2: Switch to the “Command” tab, select the “Macro” option under “Category”, and then drag the “Custom Button” option under “Command” on the right to the appropriate position on the toolbar.
Step 3: Right-click the button you just added, and in the shortcut menu that pops up, select the “Always use only text” option, and change the characters after “Name” to “Protect Formula”.
Step 4: Right-click the above button again, in the shortcut menu that pops up, select the “Specify Macro” option, open the “Specify Macro” dialog box, select the macro “Protect Formula” just made, confirm to return, and close “Automatic”. Definition dialog box.
In the future, after editing the formula in the worksheet, click the “Protect Formula” button on the toolbar, all cells containing the formula will be locked, and operations such as selection, editing, and viewing cannot be performed, which is safe and reliable.
Recommended reading：
Is Fouad WhatsApp end-to-end encrypted?
Why recommend the social app Fouad WhatsApp What are the recommendations?