Hiding formulas in excel using VBA

[narsing rao]

Hi ,

i have sheet with multiple complex formula in cell, after creating the report they are visible to all users.

Is there anyway we can hide the formulas in excel sheet using vba code. only results from formula should be visible.



Thanks,

 

[PCosta87]

Hi,

There is no need for VBA in my opinion.
The following can be done for a single cell as well as for a range:
Select the cell/range and go to the formatting options (CTRL+1). In the protection tab (last one to the right) tick the 2nd option:


Here you can see a message that tells you that this will only take effect once you protect the sheet so... protect the sheet (set a password if you want) and apply. The formulas of the cells you formatted should remain hidden, showing only the result.

Hope this helps

 

[narsing rao]

Hi Pcosta,

i will try and by the way i hope this will not effect the formula calculation.

what i am looking for is once the formula calculation happens it should hide the formula, if it hide the formula before calculation we could not able to check some values.

i got some thing like below but its not working i saved it in Thisworkbook madule

Private Sub Workbook_Activate()
    Application.DisplayFormulaBar = False
End Sub
Private Sub Workbook_Deactivate()
    Application.DisplayFormulaBar = True
End Sub

 

[PCosta87]

Hi,

For that to work you would need to use the Workbook_Open and Workbook_BeforeClose events... but I wouldn't go as far as to hide the formula bar (formula will still be visible if you edit the cell's content).
Protecting the sheet is better in my opinion, but if hiding the bar is what you want:

Private Sub Workbook_Open()
    Application.DisplayFormulaBar = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayFormulaBar = True
End Sub

 

[narsing rao]

I will try and let u know...

 

[narsing rao]

Hi Pcosta,

by this i have completed my first project of making 15 reports automated, in this your contribution is unforgettable.
and other forum members also helped me a lot.first i don't know a b c d of VB but once i was introduced to Chanddoo.org i learned a lot of coding. i came across many wonderful people who are always ready to solve any kind quarries.
i thank each and every one like you,Monty,Marc L,Chihiro to name few
you all are awesome people.

and last big thanks to Pcosta

i will come back for help with other project soon which are in loop.

 

[PCosta87]

Hi Pcosta,

by this i have completed my first project of making 15 reports automated, in this your contribution is unforgettable.
and other forum members also helped me a lot.first i don't know a b c d of VB but once i was introduced to Chanddoo.org i learned a lot of coding. i came across many wonderful people who are always ready to solve any kind quarries.
i thank each and every one like you,Monty,Marc L,Chihiro to name few
you all are awesome people.

and last big thanks to Pcosta

i will come back for help with other project soon which are in loop.

You are welcome