Excel '07 and '10 -Get Rid of Gray Screen After Closing Open Workbooks

[kjv1611]

This is an issue that's perplexed me since I moved to Excel 2007 a few years ago. I recently upgraded to 2010 at work (last week), and it's a great upgrade over 2007, I think. You get all the same features and tools from '07, it seems, but with better performance, customizable ribbon, and a new feature called Power Pivot that really is powerful.

But here's what I wanted to mention - I JUST got this to work correctly.

I have occasionally scoured the web for finding a solution to this one, and just never came across it. Today, I found the solution.

The problem is this: Beginning in Excel 2007, if you have a PERSONAL.XLS or PERSONAL.XLSB file that opens with Excel, when you close all other workbooks, you're still left looking at a gray screen for the Excel app, b/c PERSONAL.XLSB is still open - at least that's been my experience, and at least a few others - not sure how big an issue it is.

Before, I added a shortcut for "Exit Application" in the Quick Access Toolbar, but I longed for the day that I could just hit the regular Red Close button...now I'm there.

Here's how:
[ol][li]Go to Office Button ('07) or File button -> (which opens BackStage, '10)[/li]
[li]Excel Options ('07), Options ('10)[/li]
[li]Advanced[/li]
[li]Under "Display"[/li]
[li]Uncheck Show all windows in the Taskbar[/li]
[li]Close Excel[/li]
[li]Re-open Excel[/li]
[li]Re-check Show all windows in the Taskbar[/li][/ol]

If that doesn't work, try enabling or disabling via VBA:

[GREEN]'Place in a VBA Code Module:[/GREEN]
Private Sub ExcelHide()
   Application.ShowWindowsInTaskbar = False
End Sub

Private Sub ExcelShow()
   Application.ShowWindowsInTaskbar = True
End Sub

Then run each one of them.... I put it in VBA, b/c initially, I thought I'd have to flip that value back and forth to get it to work correctly.... but apparently setting it each way once fixed it.

IF you don't want to see all the windows in the taskbar, then you can just disable the feature to begin with, and you'll be totally done.

Of course, if you want to play around with the idea, here's what I first started with - one VBA procedure that does both the above, but in one.... I think it's not as good an idea, in case things get out of order in calling it..

[GREEN]'First put this in a module:[/GREEN]
Sub ExcelShowHide()
   If Application.ShowWindowsInTaskbar Then
      Application.ShowWindowsInTaskbar = False
   Else
      Application.ShowWindowsInTaskbar = True
   End If

[GREEN]'Then put this code in another module (for instance, under PERSONAL.XLSB - thisworkbook[/GREEN]
Private Sub Workbook_Open()
  ExcelShowHide()
End Sub

Using it there would run the procedure every time PERSONAL.XLSB is opened... or you could put it wherever you want... or just leave it a separate Private Sub, so it's stand-alone - whatever you wanna do.

I've not tried (so far) rebooting to see if that changes things, but if I notice any changes over the next day or so, I'll post back with that.

 

[AnotherHiggins]

I think we talked about this a long time ago, KJV.

FYI: Double clicking the Office Button will close the application, regardless of how many workbooks you have open. I know it's not the red X, but it works out of the box.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kjv1611]

Yeah, we talked about it a long time ago, but all the suggestions (that I could remember or find when I searched again) were such as that. I wanted to fix it to where I could still use the old red X. Now I can. I know it made me happy, and figured hey, somebody else might wanna know too.

 

[kjv1611]

By the way... no reboots or anything changed the effects of this - it seems to be a permanent fix. Also, I'm about to try it on another machine with the same gray screen issue. At least, I think I am. I'll post back with the results.