Solved

Minmize Excel Ribbon ONLY for the relevant workbook

Posted on 2014-09-25
16
1,412 Views
Last Modified: 2014-10-13
Hi Experts,
I'm using the below code to format my workbook upon opening; However, regardless of where I call "HidetheRibbon" it seems that if I attempt to upon another workbook/instance of excel it will minimize the ribbon there too.

I thought by defining the workbook and worksheets as variables would help as it did with hiding tabs, gridlines, formula bars, etc... but it doesn't seem to work with hiding the ribbon. Workaround?

Sub HidetheRibbon()
  If Application.CommandBars("Ribbon").Height >= 150 Then
    SendKeys "^{F1}"
  End If
End Sub
Public Sub FormatWorkbook()
  Dim wrkbk As Workbook
  Dim wrksh As Worksheet
  Dim prev As Window
Set prev = ActiveWindow
  For Each wrkbk In Workbooks
    For Each wrksh In wrkbk.Worksheets
    wrksh.Activate
    'hide excel ribbon
    Call HidetheRibbon
    'hide Formula Bar
    Application.DisplayFormulaBar = False
    'hide Headings
    ActiveWindow.DisplayHeadings = False
    'hide Gridelines
    ActiveWindow.DisplayGridlines = False
    'hide worksheet tabs
    ActiveWindow.DisplayWorkbookTabs = Flase
    'maximize excel
    Application.WindowState = xlMaximized
    'maximize windows open in excel
    ActiveWindow.WindowState = xlMaximized
  Next wrksh
Next wrkbk
prev.Activate
End Sub

Open in new window

0
Comment
Question by:-Polak
  • 9
  • 7
16 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
This thread @ Mr. Excel might have your solution.

Basically, you'll need to use the Workbook_Activate and Workbook_Deactivate events to hide and show your ribbon (respectively).

Using the SendKeys method:
Private Sub Workbook_Activate()
  If Application.CommandBars("Ribbon").Height >= 130 Then
    SendKeys "^{F1}"
  End If
End Sub

Private Sub Workbook_Deactivate()
  If Application.CommandBars("Ribbon").Height < 130 Then
    SendKeys "^{F1}"
  End If
End Sub

Open in new window


However, I prefer the method mentioned in the thread, with the possible exception of hiding the workbook tabs.  This code mimics full screen mode:
Private Sub Workbook_Activate()
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    'ActiveWindow.DisplayWorkbookTabs = False  'at your discretion
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Deactivate()
    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True  'at your discretion
    ActiveWindow.DisplayWorkbookTabs = True  'at your discretion
    Application.ScreenUpdating = True
End Sub

Open in new window


Regards,
-Glenn
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Hi Glenn, I believe that Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" gets rid of the users ability to access the ribbon altogether and I would still like to have them retain the ability to set custom print areas and such if they need too, that's why I am just minimizing it (for now).

Question, the above code uses 130 and I used 150, is that an excel version thing? If so, which should I use to be apply to all versions of excel 2007 and on?
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
The size of the ribbon is a Windows-environment issue.  In my case, my ribbon was 147 in height, so I just used those values arbitrarily to get it to appear and hide.  Not a fan of that, admittedly.

If 150 is working for all your environments, then stay with that.

-Glenn
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Yeah.... I don't like that much either given that there's the potential for the ribbon to be smaller than 130 on some user with a really high resolution screen..... what about a hidetheribbon macro that measures the height of the ribbon maximized (height 1) then measures the height minimized (height 2) and says if height 1 is >= to height2 then Send Shift F1, and for showtheribbon if height2 is <= height1 Send Shift F1? I just tried to write this but I'm having trouble...... is it possible?

Also, not that this matters but I found it interesting that Workbook activate / deactivate only work if you open another excel workbook you have saved, but not if you just open the excel application again. Quirky.....
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Finding more problems... noticed for hide/show the ribbon in the Excel 2013 environment SendKeys "^{F1}" must be Application.SendKeys "^{F1}"

Here's the code as it stands now:
Private Sub Workbook_Activate()
Application.ScreenUpdating = False
Call HidetheRibbon
    'hide Formula Bar
    Application.DisplayFormulaBar = False
    'hide Headings
    ActiveWindow.DisplayHeadings = False
    'hide Gridelines
    ActiveWindow.DisplayGridlines = False
    'hide worksheet tabs
    ActiveWindow.DisplayWorkbookTabs = False
    'maximize excel
    Application.WindowState = xlMaximized
    'maximize windows open in excel
    ActiveWindow.WindowState = xlMaximized
End Sub
Private Sub Workbook_Open()
'activate StandardMode
Sheets("StandardMode").Activate
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.ScreenUpdating = False
    Call UsedRangeZoom
End Sub
Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
Call ShowtheRibbon
    'hide Formula Bar
    Application.DisplayFormulaBar = True
    'hide Headings
    ActiveWindow.DisplayHeadings = True
    'hide Gridelines
    ActiveWindow.DisplayGridlines = True
    'hide worksheet tabs
    ActiveWindow.DisplayWorkbookTabs = True
End Sub

Open in new window

and
Sub HidetheRibbon()
  If Application.CommandBars("Ribbon").Height >= 130 Then
    Application.SendKeys "^{F1}"
  End If
End Sub
Sub ShowtheRibbon()
  If Application.CommandBars("Ribbon").Height < 130 Then
    Application.SendKeys "^{F1}"
  End If
End Sub
Sub UsedRangeZoom()
    'set zoom for active sheet to columns with data
    ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Columns.Count).Select
    ActiveWindow.Zoom = True: ActiveWindow.VisibleRange(1, 1).Select
End Sub

Open in new window


Aside from the worry concerning Ribbons that could potentially be smaller than 130px I've found that even if I create a Workbook_BeforeClose event for ShowtheRibbon when I got to open another workbook once this one has been closed the ribbon is still minimized. I've also tired Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)", but to no avail; not matter what I try the ribbon is minimized when I try and go open a new workbook. Here's the file...
EESafe-UI-Input-09.24.xlsm
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
I had a couple of issues on opening your workbook:
1) The SendKeys command wasn't recognized properly - it called up Excel Help [F1] instead of using [Ctrl]+[F1]
2) Because of that, there appears to be an issue with what window is active (Excel or VBA) and then the macro might halt.
3 When I tried opening another file, I ran into UI issues with that macro-based app (which happens to be one of mine that does similar applications, but affects the entire Excel session)

Maybe we need to step back a second. :-)

Maybe instead of hiding the ribbon, editing gridlines and such, how about just going into Full Screen mode while the workbook is active?  This will effectively remove all the menu items, leaving only the formula bar (which is recommended).  In design mode, turn off the horizontal, vertical bars, sheet tabs, gridlines.  If you want to turn them on for design purposes, then create a private subroutine that turns them on for convenience.

That said, I can see the following Worksheet events being created:
Private Sub Workbook_Activate()
    Application.DisplayFullScreen = True
End Sub

Open in new window


Private Sub Workbook_Deactivate()
    Application.DisplayFullScreen = False
End Sub

Open in new window


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayFullScreen = False
End Sub

Open in new window


Private Sub Workbook_Open()
    Application.CalculateFull
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayFullScreen = True
 
    'Used Range Zoom code embedded
    ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Columns.Count).Select
    ActiveWindow.Zoom = True: ActiveWindow.VisibleRange(1, 1).Select
    Application.ScreenUpdating = True
End Sub

Open in new window


Applying this logic - especially the Active/Deactivate event to an existing application that I have that runs in full screen mode and it works very well.
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Oouuuuuuffff, hokay; so, yes, the reason I'm avoiding full screen is I still want to retain the ability for end-users to do some of the regular excel stuff, like filtering/sorting etc... (if they want too) especially in some of the data in the parameters and baseline worksheets that you're not seeing.

BUUUUUUUUUTTTT I'm not married to "my way"; yet, having to make custom macros to set print areas/print reports and worksheets sounds like a pain in the butt. That said, your above code does work... but I'm curious to explore some of the bugs that you are experiencing and seeing if we can easily find the reasons behind them.  

With the file I attached in my previous post (in excel 2007 and excel 2013) the only issue I am experiencing is that when I close the workbook the ribbon remains minimized when I open another instance of excel. If I switch between workbooks the ribbon minimizes and maximizes correctly upon activation/deactivation

Addressing your specific issues:
1) The SendKeys command wasn't recognized properly - it called up Excel Help [F1] instead of using [Ctrl]+[F1]
 2) Because of that, there appears to be an issue with what window is active (Excel or VBA) and then the macro might halt.
Do you have some sort of error prompt popping up when you open the workbook; I know that when I tried to run Application.SendKeys "^{F1}" on "Workbook_BeforeClose" the help window popped up because if you press CTRL + F1 when excel is asking you "Do you want to save changes" the help window will come up. Or perhaps have you've customized excel so that VB opens when excel opens and is the active window?
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Just reporting behavior to nail it down.  This is of your last-supplied copy (EESafe-UI-Input-09.24.xlsm)

1) If the workbook is opened in a new session of Excel (i.e., no other open workbooks), there are no issues with the open, but the ribbon is visible.  When the document is closed, the ribbon is hidden.
2) If the workbook is opened in a new session of Excel and VBA is open (minimized), the macro fails in the Workbook_Open subroutine on the Sheets("StandardMode").Activate line.

3) If the workbook is opened in an existing session of Excel with another workbook already open, there are no issues with opening.  However, the ribbon is visible.  If one switches to another workbook, the ribbon is hidden.  Switching back to this workbook does not show the ribbon.  Switching again to another workbook shows the ribbon.

It appears that only when the file is deactivated is the SendKey command executed.  I think this is because - in my environment - the maximum height of the ribbon is only 147, so the HidetheRibbon sub never executes for me.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
Comment Utility
How about changing the threshold in the Show/Hide subroutines to 100?  That worked perfect for me.
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Yep, I was going to suggest that next (I thought the code in my previous file was @ 130 vs. 150) .
2) If the workbook is opened in a new session of Excel and VBA is open (minimized), the macro fails in the Workbook_Open subroutine on the Sheets("StandardMode").Activate line.
I can't seem to duplicate this behavior, but no matter that code is really superfluous now that I think about it.

With it changed to 100, is the ribbon maximized or minimized in a new instance of excel after closing the workbook that I previously attached?
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Once I changed both thresholds to 100, the behavior worked as-expected.  I had no issues at all.
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
Interesting, here are the issues I'm experiencing the following environments:

Excel 2007
Other Workbooks Open: no issues, switching back and forth between workbooks correctly shows/hides the ribbon in the active workbook and upon closing the EESafe-UI-Input workbook the ribbon reappears in other workbooks that are open. PERFECT
No Other Workbooks Open:upon closing Excel using the Excel "X" not the EESafe-UI-Input workbook's "X" the ShowtheRibbon sub does not appear to run once the workbook is deactivated and then closed. Therefore, upon opening another instance of excel or another workbook the ribbon remains minimized.

Excel 2013
Other Workbooks Open: no issues, switching back and forth between workbooks correctly shows/hides the ribbon in the active workbook. HOWEVER, when you close the workbook it WILL hide the ribbon in other open workbooks (this is likely because, unlikely 2007, each instance of excel is a separate application window)
No Other Workbooks Open: identical behavior to Excel2007 (ribbon is minimized when opening another workbook/instance of Excel after closing EESafe-UI-Input workbook)

If I'm between using Fullscreen=true/false and having to tell 2007 users to deal with their Excel ribbon's being minimized after they close this workbook and 2013 users that if they close this workbook it will minimize the ribbon in all of their other open workbooks. I will choose the fullscreen route; however, if you aren't experiencing workbook-interplay behavior issues I'd like to know how/why and then try to duplicate it working correctly.

Most recent workbook is attached.
EESafe-UI-Input-09.29.xlsm
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
I'm only testing in Excel 2010 and that is a single-window environment.  I (almost) never run more than one instance of Excel.

Are these scenarios happening after the change in the ribbon height criteria test (100)?
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
yes sir, 100 for the hide & show.
0
 
LVL 1

Author Closing Comment

by:-Polak
Comment Utility
The fullscreen solution work flawlessly in all excel versions/environments. However, if you wish the ribbon to remain minimized so that users can utilize some of excels basic features it appears that even with the workbook_deactivate code the ribbon will remain minimized upon exiting excel and opening a new session. Not the end of the world, but I'm going to hate answering all of the "where's my ribbon" questions.
0
 
LVL 1

Author Comment

by:-Polak
Comment Utility
I've noticed an issue with the above code that essentially clears the clipboard when its run preventing the end-user from copying and pasting between workbooks/worksheets, I've found an initial workaround/solution in this follow-up question, but if you think you can provide something better please let me know.... http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28535628.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now