• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide cell content and value from formula bar without protecting cells

Hany ali

Active Member
good evening ,my dear professors ... I hope you are all in the best condition, I ask you for an excuse for helping me with this problem, which is that I made a coordination of the cells to hide their content and this is the format used and as it is found in the file in the second column B starting from cell B2
But I was surprised by the appearance of the values and content of cells on the formula bar ... Is there a way to hide the value of cells also on the formula bar or appear in the form of stars, and that all of this be without hiding the formula bar because I need it a lot in formulas in other cells, or without protecting cells. . And Thank you very much



Active Member
You only have to use this code in the event Sheet
Dim xDic As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim xCell As Range
    Dim xRg As Range
    Set xRg = Range("b2:b100")
    If xDic.Count <> xRg.Count Then
        For Each xCell In xRg
            xDic.Add xCell.Address, xCell.FormulaR1C1
    End If
    If (Target.Count = 1) And (Not Application.Intersect(xRg, Target) Is Nothing) And (Target.HasFormula) Then
        With Target
            .Value = .Value
        End With
        For Each xCell In xRg
            xCell.Formula = xDic.Item(xCell.Address)
    End If
End Sub