October 22, 2018

Hide Formula in Worksheet Without Protecting Worksheet

How to hide a formula in worksheet without protecting it with a password using VBA. We’ll use the collection object Dictionary to achieve our goal. One of the best articles on the Dictionary collection object is available at this website.

Watch the video below:

Watch this video on YouTube.

Here are the main points for solving our problem with reference to the Dictionary variable taken from the link mentioned above:

  • A Dictionary in VBA is a collectionobject: you can store all kinds of data in it: numbers, texts, dates, arrays, ranges, variables and objects.
    ‘Every item in a Dictionary gets its own unique key.
  • With that key you can get direct access to the item (reading/writing/adapting).
    ‘The way in which the Dictionary stores items is comparable to the Collection object.
  • The Dictionary has some properties/functions/methods like .keys, .items and .removeall, that can be advantageous compared to the use of a collection.
    ‘Generally speaking you can use a Dictionary to store/group data that have a common property (key).
  • Scattered data can be integrated easily.
  • You can group all kinds of data in a Dictionary to get easy and quick access to them since they are temporarily stored in memory.
    ‘Instead of manipulating data in an Excel-worksheet on the hard disk, you can do that in memory.
  • No screenrefreshing, no autocalculation, no background backup is necessary, so it will speed up your code considerably.
  • A Dictionary can only contain unique keys. That’s why you can use the property .keys to create a list of unique strings, numbers or dates.
  • The Dictionary isn’t part of the standard VBA library.
  • It ‘s an element in the Microsoft Scripting Runtime library.

Here’s the complete VBA code:

Dim myDic As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Set myRng = Range(“D2:D6”)

If myDic.Count <> myRng.Count Then
For Each myCell In myRng
‘myCell.Address is the key, myCell.FormulaR1C1 is the content
myDic.Add myCell.Address, myCell.FormulaR1C1
Next
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
‘ MsgBox myDic.Count
myDic.RemoveAll
End Sub

Further Reading:

Dictionaries

Sample file for download and practice:

3 thoughts on “Hide Formula in Worksheet Without Protecting Worksheet

  1. Please add me to your email list for updating me with your VBA tricks & tips, etc
    I am already a subscriber of your Yt channel.

  2. My excel is not working same method. What I can do? Can I get same excel sheet with VBA

    Thanks And Regards,

    Roy Varghese.

Comments are closed.