• 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.

VBA code for hiding formulas


New Member
Hi All,

I have a problem and don't know how to sort it, hopefully someone can help me because I am losing my mind......
So, I have already two codes, but I need one more, I think...
When my file is protected then formulas that are in the certain range are hidden but when my file is unprotected then my formulas are visible..
I would like to put a code that will hide the formulas when the file is unprotected for the range from I4:BF153

I am really new in this and the codes that I have are taken from internet - when I need something I just google it :rolleyes:
I needed a code for grouping ungrouping when file is protected so the below is the one that I used and the second one is I don't know for what anymore....
Please don't kill me.....

>>> use code - tags <<<
Private Sub Workbook_Open()
Dim mySheet As Worksheet
    Set mySheet = Application.ActiveSheet
    Dim myPW As String
    myPW = Application.InputBox("Type one Password to protect your worksheet:", "allowGroup", "", Type:=2)
    mySheet.Protect Password:="Zero", userinterfaceonly:=True
    mySheet.EnableOutlining = True
End Sub

Private Sub Workbook_Open()
Dim wsh As Worksheet
For Each wsh In Me.Worksheets
wsh.EnableOutlining = True
wsh.Protect Password:="Zero", userinterfaceonly:=True
Next wsh
End Sub
Hopefully someone can help me please......

Last edited by a moderator:


Excel Ninja
Would You reread Forum Rules?
There are useful hints - eg - How to get the Best Results at Chandoo.org

Yes -
You have two codes ... and ... You're thinking to get one more ...
I am really new in this and the codes that I have are taken from internet - when I need something I just google it

... copy & paste could work too, if ... if ... if ...
Do You have an idea - how many Workbook_Open -codes can use with one Excel-file?
Where are Your Workbook_Open -codes?
Could You upload a sample Excel-file?

Based Your Unfortunately is not working, don't know why...
Do You know, that it needs 'Excel-friendly-coding' to get something, which is ... working?


Active Member
Ok ... this works here ... tested.

Place in the Sheet Module :

Option Explicit

Dim myDic As New Dictionary

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range
Set myRng = Range("A1:B1")

    If myDic.Count <> myRng.Count Then
        For Each myCell In myRng
            myDic.Add myCell.Address, myCell.FormulaR1C1
    End If
    If (Target.Count = 1) And (Not Application.Intersect(myRng, Target) Is Nothing) And (Target.HasFormula) Then
        With Target
            .Value = .Value
        End With
    End If

End Sub
You will also need to include a reference to the Microsoft Scripting Runtime Library.