1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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

    Hui...

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

Hiding formulas in excel using VBA

Discussion in 'VBA Macros' started by narsing rao, Apr 20, 2017.

  1. narsing rao

    narsing rao Member

    Messages:
    108
    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,
  2. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    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:
    1.JPG

    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 likes this.
  3. narsing rao

    narsing rao Member

    Messages:
    108
    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
    Code (vb):


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

    PCosta87 Well-Known Member

    Messages:
    870
    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:
    Code (vb):
    Private Sub Workbook_Open()
        Application.DisplayFormulaBar = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Application.DisplayFormulaBar = True
    End Sub
    Thomas Kuriakose and narsing rao like this.
  5. narsing rao

    narsing rao Member

    Messages:
    108
    I will try and let u know...
  6. narsing rao

    narsing rao Member

    Messages:
    108
    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.
    Thomas Kuriakose likes this.
  7. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    You are welcome ;)

Share This Page