How can I make my cells that contain functions read only at the same time make them hidden in excel-CodePudding

Here is my sample code but doesn't work

Sub HideAllFunctions()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Cells.EntireColumn.Hidden = True
Next ws
End Sub

CodePudding user response：

Your code simply hides all cells of a worksheet. If you want to hide cells that contain a formula, you could use

ws.Cells.SpecialCells(xlFormulas).EntireColumn.Hidden = True

However, as it is only possible to hide entire columns or rows, this would hide all columns where at least one cell has a formula - not sure if this is what you want (you could use EntireRow instead, same problem, the whole row is hidden).

To set a cell to readOnly, you will need to set the locked-property of the cell and switch on sheet protection. However, as the locked-property is switched on for all cells by default, you will need to unprotect those cells first that should be editable.

If you switch on sheet protection, you can switch on FormulaHidden - the formula is now not shown in the formula bar.

If you use SpecialCells and no matching cell can be found (eg no formulas on a sheet), VBA will raise a "No cells found" runtime error. To prevent the error, disable error handling for that statement and check if something was returned:

Dim formulas As Range
Set formulas = Nothing
On Error Resume Next
Set formulas = ws.Cells.SpecialCells(xlFormulas)
On Error GoTo 0

ws.Cells.Locked = False              ' Enable Editing for all cells
If Not formulas  Is Nothing Then
    ' formulas.EntireColumn.Hidden = True  ' Are you sure???
    formulas.Locked = True           ' Lock cells with formula 
    formulas.FormulaHidden = True    ' Hide formulas
End If
ws.Protect UserInterfaceOnly:=True   ' Protect sheet