Results 1 to 10 of 10

hide formula bar

This is a discussion on hide formula bar within the Excel Questions forums, part of the Question Forums category; I am planning to email an excel spreadsheet as a survey. Is there any way to hide the formula bar? ...

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Location
    Ridgetown Ontario Canada
    Posts
    190

    Default hide formula bar

    I am planning to email an excel spreadsheet as a survey. Is there any way to hide the formula bar? I would prefer that the user not see my formulas when they click on a cell. I can hide the formula within the sheet, but can't find out if I can prevent the user from seeing the formula in the formula bar. Is this possible?

    Rick

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    Click on Tools-Options-View and uncheck the Formula Bar box.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default

    You have to options:

    One, use:

    Application.DisplayFormulaBar = False

    two, select the cells that have the formulas you want to hide, press Ctrl 1, and in the "Protection" tab, check the "Hidden" box that you see there.

    After that, protect the sheet, and the user won't be able to see the formulas, even tough the display the formula bar.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    Board Regular
    Join Date
    Aug 2002
    Location
    Ridgetown Ontario Canada
    Posts
    190

    Default

    Thank-you - that was exactly the step I was missing. (I used option 2!)
    Rick

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Posts
    114

    Default

    Quote Originally Posted by Juan Pablo González
    You have to options:

    One, use:

    Application.DisplayFormulaBar = False

    two, select the cells that have the formulas you want to hide, press Ctrl 1, and in the "Protection" tab, check the "Hidden" box that you see there.

    After that, protect the sheet, and the user won't be able to see the formulas, even tough the display the formula bar.
    Hi Juan,

    In which procedure do I need to enter the following code?
    Application.DisplayFormulaBar = False

    Also do I need to enter in one sheet or for all sheets? Sorry if the question sounds stupid... I'm relatively new to VBA :o
    TIA,

    -J

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default Re: hide formula bar

    In what procedure ?

    That's up to you... in the one where you want to hide the formula bar

    And no, that's one setting that affects all sheets, all workbooks, so you only need to enter it once.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Board Regular
    Join Date
    Dec 2002
    Posts
    114

    Default Re: hide formula bar

    Quote Originally Posted by Juan Pablo González
    In what procedure ?

    That's up to you... in the one where you want to hide the formula bar

    And no, that's one setting that affects all sheets, all workbooks, so you only need to enter it once.
    Hi Juan,

    Sorry if I was unable to clrealy explain my question

    Here's what I meant:

    When I go in the Visual Basic Editor, do I enter the code in the worksheet's 'Activate' Sub or 'Change' sub or ??? I tried it in the 'Activate' Sub but that does'nt work

    Again, I know my question sounds stupid so please bear with me

    TIA,

    - J

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default Re: hide formula bar

    If you want it to happen once you open or activate the workbook, then go into the ThisWorkbook module and add this:-

    Code:
    Private Sub Workbook_Activate()
    
    Application.DisplayFormulaBar = False
    
    End Sub
    
    Private Sub Workbook_Deactivate()
    
    Application.DisplayFormulaBar = True
    
    End Sub
    So, when you activate the workbook containing the code the formula bar will be hidden, when you activate another workbook it will be visible.

  9. #9
    Board Regular
    Join Date
    Mar 2003
    Posts
    76

    Default Re: hide formula bar

    This "hide" code works pretty nice. I am not a VB expert but how would I hide ALL the toolbars, formula bar, status bars, sheet tabs and scroll bars via VB [in similar manner as described above]?

    I know I can go under options and disable it but so can everyone else and enable it all.

    Also how can I set my Excel window size to specific dimension when my worksheet is open? by default it goes to 800x600. What if I want 300x200 and disable resizing?
    thanks

  10. #10
    New Member
    Join Date
    Jan 2010
    Location
    Kiev, Ukraine
    Posts
    30

    Default Re: hide formula bar

    You could try this:

    Code:
     
    Dim DisabledBars As New Collection
    Sub DisableToolBars()
    On Error Resume Next
        For Each tb In Application.CommandBars
            If tb.Enabled Then
                DisabledBars.Add tb.Name, tb.Name
                tb.Enabled = False
            End If
        Next
        Application.DisplayFormulaBar = False
        Application.DisplayStatusBar = False
    On Error GoTo 0
    End Sub
    Sub EnableToolBars()
        If DisabledBars.Count > 0 Then
            For Each tbname In DisabledBars
                Application.CommandBars(tbname).Enabled = True
            Next
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
        End If
    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com