Hide Formulas in a Worksheet and Prevent Deletion on "merged cells"



The follwing Tutorial was postad a few wiks ago to Hide Formulas in a Worksheet and Prevent Deletion,


#1 I have in my sheet merged cells that contains formulas and when I select this cell the excel sheet start to freeze, is there any way this function should work also of the merged cells?

#2 I have some formulas what I don't want to lock and hide, so I try to add this line of  code 

If Target.formula = "=IF(C28="","",4)" Then Exit Sub
but it doesn't work, can anyone help me with this 2 questions?

thank you



Selected Answer

It seems to me that the code you are using represents a bit of an over-kill in that it treats all sheets in the workbook equally. Please try the code below. Install it in the code sheet of the worksheet on which you want the action. I think it will have the same functionality you already know. If not, tell me where I'm wrong. It will not balk at merged cells/

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 27 Oct 2017
    Const Pw As String = "password"
    Dim CellsWithFormula As Range
    Dim n As Long
    With Target
        On Error Resume Next
        n = .Cells.Count
        ' if the entire sheet is selected,
        ' only the first cell's merge area will be processed
        If Err Then Set Target = .Cells(1).MergeArea
        On Error GoTo 0
    End With
    With Target
        Set CellsWithFormula = .SpecialCells(xlCellTypeFormulas)
        If Not CellsWithFormula Is Nothing Then
            Unprotect Password:=Pw
            With CellsWithFormula
               .Locked = True
               .FormulaHidden = True
            End With
            Protect Password:=Pw, UserInterFaceOnly:=True
        End If
    End With
End Sub

As for the cell you wish to excclude, the description of it you give is insufficient. The best way to identify it would be by its location. If it's a range, then the range's location. If it must be identified by the formula it holds, the exact formula is needed, as it appears in the cell, including $ signs.



Hi Variatus
JAExcel (rep: 10) Oct 30, '17 at 10:10 am
Interesting!<br />#1 Two seconds point to a loop running a few tenthousand times. Place a Debug.Print "Here" at the top of the code to see if it is called repetitively. If yes, Set Application.EnableEvents = False at
Variatus (rep: 1328) Oct 30, '17 at 11:56 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login