Got any Excel Questions? Excel Help
Stop Users Seeing Your Excel Formula
The normal method to stop formula viewing is to Hide Formulas via Format>Cells - Protection and check Hidden. Then apply Worksheet Protection. You would normally check the Locked box (checked by default) which would also prevent formula deletion.
TIP: Use F5 - Special to select specific cell types
The draw back with the standard method is that the entire Worksheet is protected and hence stopping many other features from being used when not even in a formula cell. The VBA method below, using Workbook_SheetSelectionChange, SpecialCells Method and the UserInterFaceOnly Argument of the Protect Method . is a excellent work-around. Open the VBE (Alt+F11) then double click ThisWorkbook to access the private module of the Workbook Object.
What the code will do is Lock and Hide all Formula from viewing and protect the Worksheet by only locking and hiding anytime a Selection contains one or more Formula cells. Only the formula cells are Locked and Hidden. If there are no formulae cells in the selection, all cells within are unlocked and not hidden.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim rFormulaCheck As Range On Error Resume Next Sh.Unprotect Password:="Secret" With Selection .Locked = False .FormulaHidden = False End With If Target.Cells.Count = 1 Then If Target.HasFormula Then With Target .Locked = True .FormulaHidden = True End With Sh.Protect Password:="Secret", UserInterFaceOnly:=True End If ElseIf Target.Cells.Count > 1 Then Set rFormulaCheck = Selection.SpecialCells(xlCellTypeFormulas) If Not rFormulaCheck Is Nothing Then With Selection.SpecialCells(xlCellTypeFormulas) .Locked = True .FormulaHidden = True End With Sh.Protect Password:="Secret", UserInterFaceOnly:=True End If End If On Error GoTo 0 End Sub
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates