 |
|
|
|
|
|
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 special@ozgrid.com 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
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS