hide formula bar

ridgetown_rick

Board Regular
Joined
Aug 28, 2002
Messages
193
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
Juan Pablo González said:
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 :eek:
TIA,

-J
 
Upvote 0
In what procedure ?

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

And no, that's one setting that affects all sheets, all workbooks, so you only need to enter it once.
 
Upvote 0
Juan Pablo González said:
In what procedure ?

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

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 :cry:

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

TIA,

- J
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top