Solved

Hide the Excel Formula Bar in only one of multiple open workbooks

Posted on 2014-01-31
5
2,583 Views
Last Modified: 2014-02-28
Hello,

Suppose you have multiple Excel (2013) workbooks open at the same time. Is there a way to hide the Formula Bar in one of the workbooks without it also disappearing in the others?

Each of the other two items (gridlines & headings) under the View menu can be changed in only a single workbook but I cannot determine how to do the same with the Formula Bar.

Thanks
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 300 total points
ID: 39825295
Hi Steve,

The visibility of the formula bar is application-level, meaning you can't inherently give it different settings for different workbooks.

If you're willing to use macros, however, we can accomplish what you want. Press Alt-F11 to open the VBA editor, then press control-r to open/focus on the Project window. Look for the workbook you want it to be hidden in, and expand the "Microsoft Excel Objects" folder. Double click the "ThisWorkbook" icon, and in the codepane that appears, paste in the following:
Private Sub Workbook_Activate()
 Application.DisplayFormulaBar = False
End Sub

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

Open in new window


An alternate approach without macros would be to hide your cell values/formulas and protect the sheet. This hides the formula from showing up in the formula bar.
Select your cells, press control-1 to open the Format Cells window, and go to the Protection tab. Check the box for Hidden, and press OK. Then, protect the worksheet. The formula bar will be blank for the Hidden cells.

Matt
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 39829026
If you force that file to open a new instance of Excel it will have its own formula bar to hide.

Thanks
Rob H
0
 

Author Comment

by:Steve_Brady
ID: 39848768
Thanks for the responses.

Matt,

Your VBA Solution seems to be the best option as hiding formulas is not really the objective, just hiding the formula bar itself.

------------------------------------------------------------

Rob,

Can you elaborate a bit? I don't understand your comment (eg "that file").

Thanks again
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 39848779
Open a separate instance of excel by running the shortcut from the start menu and in the new instance of excel open the file for which you want to hide the formula bar.
0
 

Author Closing Comment

by:Steve_Brady
ID: 39896372
Thanks
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question