Hide Formulas

[arnapsui]

Hi

I want to hide the formula that I have entered in a cell. I already know that I can use Format Cells and Protection tab to hide a formula, but I need to protect the sheet. I read long time ago to use the Format Cells and Number tab (Custom) which I cannot remember now.

Can anyone please help me?

 

[Tom Urtis]

Hide the formula in what way? If you custom format the cell as ;;; (that is, three semicolons) you will only hide what the formula returns, which will not be very practical. Plus, the formula will still be visible in the Formula Bar when the cell is selected, so maybe you want to optionally hide the Formula Bar too. Not clear exactly what you are after.

 

[arnapsui]

Thanks a lot for your quick reply.

What I exactly need is to hide the formula and show only the result in the cell and the formula bar even if the sheet is unprotected.

 

[Tom Urtis]

You are asking for more than Excel is meant to do in its native non-VBA environment. You want the sheet unprotected and a formula in a cell to not be seen in the formula bar or in the cell. And you want to keep the formula bar visible and the cell visible.

One way to do what you want is to decide upon some out-of-the-way cell, such as maybe AA1, where you can place the actual formula. Let's say that cell D5 is the cell that should only show the returned value.

Right click on your sheet tab, left click on View Code, and paste this procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Calculate()
Range("D5").Value = Range("AA1").Value
End Sub

Hide column AA.

This will achieve the visual effect you say you want.