VBA: Hide ribbon & others, how to limit to one workbook?

[MrsAlice]

Hello,

The issue
On opening the file my code hides the:

• ribbon
• formula bar
• status bar
• tabs

When the file is closed it turns the aforementioned back on. This all works fine until you start working in several excel files simultaneously: the ribbon etc. are hidden on all open excel files.

Is it possible to limit the effects of the code to this single workbook in the VBA code itself? I can only think of a workaround by opening this file in a separate Excel-instance, but this has some serious disadvantages. Workbook_Activate & Workbook_Deactivate looked promising, but can't get it to work.

The current code on opening (some code omitted, it is in ThisWorkbook)

Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False
End Sub

The current code on closing (some code omitted, it is in ThisWorkbook)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True
End Sub

Some context
- I have scoured the web for people asking similar questions, got some results, but no real answer.
- I have made passive use of MrExcel and Ozgrid for a lot of issues, but this is my first post. I hope I am doing things right. This is not being crossposted. I will check up on this topic frequently.

Thank you for your time. Without communities such as these I would not be able to learn so much about Excel and VBA as I am now.

 

[Andy Pope]

You also need to add code to the Activate and deactivate events.

Private Sub Workbook_Activate()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False

End Sub

Private Sub Workbook_Deactivate()

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True

End Sub

 

[MrsAlice]

Thanks for the response Andy Pope.

I have previously tried to simply switch from Workbook_Open/close to Workbook_Activate/Deactivate, but to no avail. The code in the opening post is simply the code I am currently using, not something I was trying Sorry for the confusion.

When I switch as mentioned before (I also removed the Boolean declaration and remove the setRange / select.Myrange in both subs) it simply bounces back to the file that contains this code, without turning the interface back on in the other workbooks.

To rephrase my question: Below is the complete code that I am currently using to hide the Excel interface for 1 single workbook. It also hides the interface in other workbooks. How do I prevent this from happening? Are Workbook_Activate & Deactivate the right tools for the job? If not, what is? Is it possible at all without opening a separate Excel instance?

Private Sub Workbook_Open()
Set myRange = ActiveSheet
   Application.ScreenUpdating = False
   Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
   Application.DisplayFormulaBar = False
   Application.DisplayStatusBar = Not Application.DisplayStatusBar
   ActiveWindow.DisplayWorkbookTabs = False

   Dim wbBook As Workbook
   Dim wsSheet As Worksheet

   Set wbBook = ThisWorkbook

   For Each wsSheet In wbBook.Worksheets
      If Not wsSheet.Name = "Blank" Then wsSheet.Activate
         With ActiveWindow
            .DisplayHeadings = False
            .DisplayGridlines = False
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True,    Scenarios:=True, AllowFormattingRows:=True
            'ActiveSheet.EnableSelection = xlUnlockedCells
         End With
   Next wsSheet

myRange.Select

End Sub

and

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set myRange = ActiveSheet
   Application.ScreenUpdating = False
   Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
   Application.DisplayFormulaBar = True
   Application.DisplayStatusBar = True
   ActiveWindow.DisplayWorkbookTabs = True

   Dim wbBook As Workbook
   Dim wsSheet As Worksheet

   Set wbBook = ThisWorkbook

   For Each wsSheet In wbBook.Worksheets
      If Not wsSheet.Name = "Blank" Then wsSheet.Activate
         With ActiveWindow
           .DisplayHeadings = True
           .DisplayGridlines = True
           ActiveSheet.Protect DrawingObjects:=True, Contents:=True,  Scenarios:=True, AllowFormattingRows:=True
          'ActiveSheet.EnableSelection = xlUnlockedCells
         End With
   Next wsSheet

myRange.Select
End Sub

 

[Andy Pope]

You need to duplicate the workbook_open event code in the Activate event and the BeforeClose in the Deactivate event.

 

[MrsAlice]

[Solved]
It works now. A minor nuisance is that once in a while the screen I am switching to (including programs like Firefox) turns completely black. It returns to normal after switching once or twice, so it's no biggy.

The combination of the code in my 2nd post and this one is probably suboptimal, but it works.

The code that did it, for future generations :

Private Sub Workbook_Activate()

    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False
    Application.ScreenUpdating = True

End Sub

Private Sub Workbook_Deactivate()

    Application.ScreenUpdating = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True

 End Sub

 

[guigui971]

Hi all,

I come here with a problem related to it.
I have a workbook with multiple sheets, for which I want to leave EXCEL appear as little as possible, because the user only need direct input into the sheets, without the need of different menus EXCEL, and because I want to use maximum screen.

During the use of this "special" workbook, it may be necessary to use another, who needs the aspect of "normal" EXCEL ...

I created different events placed in "ThisWorkbook" supposed to do all this:

Sub Workbook_Open()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
Application.DisplayFullScreen = True
Application.DisplayStatusBar = False '(here I hesitate because some, like here, put "Not Application.DisplayStatusBar" instead of "False")
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
End Sub

Sub Workbook_activate()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
Application.DisplayFullScreen = True
Application.DisplayStatusBar = False
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
Application.DisplayFullScreen = False
ActiveWindow.View = xlNormalView
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayGridlines = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
Application.DisplayFullScreen = False
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
ActiveWindow.View = xlNormalView
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayGridlines = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
Application.DisplayFullScreen = False
Application.ScreenUpdating = True
End Sub


The problem is this code does not work every time:
- When I switch to another workbook ("normal"), the ribbon and the formula bar stay usually (not always!) hidden, while header rows / column, and the grid are appearing.
- When I pass from one sheet to another on my "special" workbook, the formula bar sometimes returns.
- Same when I go back from my "normal" workbook to my "special" workbook, the formula bar and / or header rows / column sometimes reappear ...

I tried to slow down my code by adding a small loop between each line: no effect!

And I did not find any answer in the web, except here.

In short, after having tried everything, I'm lost!

Thank you in advance for your advice

 

[guigui971]

Hi,

I've found a solution!

In thisworkbook :

Sub Workbook_Open()​

Application.EnableEvents = False​

Call masque​

Application.EnableEvents = True​

End Sub​

​

Sub Workbook_Activate()​

Application.EnableEvents = False​

Call masque​

Application.EnableEvents = True​

End Sub​

​

Sub Workbook_Deactivate()​

Application.EnableEvents = False​

Call normal​

Application.EnableEvents = True​

End Sub​

​

Sub Workbook_BeforeClose(Cancel As Boolean)​

Application.EnableEvents = False​

Call normal​

Application.EnableEvents = True​

ThisWorkbook.Saved = True​

End Sub​

In module1

Sub masque()​

Application.ScreenUpdating = False​

Application.EnableEvents = False​

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"​

ActiveWindow.DisplayHeadings = False​

ActiveWindow.DisplayGridlines = False​

Application.DisplayFullScreen = True​

Application.DisplayStatusBar = Not Application.DisplayStatusBar​

Application.WindowState = xlMaximized​

ActiveWindow.WindowState = xlMaximized​

Application.DisplayFormulaBar = False​

Application.EnableEvents = True​

Application.ScreenUpdating = True​

End Sub​

In module2

Sub normal()​

Application.ScreenUpdating = False​

ActiveWindow.View = xlNormalView​

ActiveWindow.DisplayHeadings = True​

ActiveWindow.DisplayGridlines = True​

Application.DisplayStatusBar = True​

ActiveWindow.DisplayHorizontalScrollBar = True​

ActiveWindow.DisplayVerticalScrollBar = True​

Application.DisplayFullScreen = False​

Application.DisplayFormulaBar = True​

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"​

'Curiously, if we put the previous line at the beginning of the module, it is not taken into account each time ...​

Application.ScreenUpdating = True​

End Sub​

In each sheet :

Sub Worksheet_Open()​

Call masque​

End Sub​

​

Sub Worksheet_Activate()​

Application.ScreenUpdating = False​

Call masque​

Application.ScreenUpdating = True​

End Sub​

​

Sub Worksheet_Deactivate()​

Application.ScreenUpdating = False​

Call normal​

Application.ScreenUpdating = True​

End Sub​

​

Sub Worksheet_BeforeClose(Cancel As Boolean)​

Call normal​

End Sub​

At the beginning, it gets full screen and EXCEL appears at least for all sheets in the workbook *


When it is closed, or another spreadsheet is opened, this last appears in the usual environment EXCEL.




* Small problem though: when you go form a sheet to another, there is a small moment during EXCEL interface that re-appears. While I have put Application.ScreenUpdating = False. .... If someone has an idea ...


thank you

 

[guigui971]

I add a correction to last post

Hi,

I've found a solution!

In thisworkbook :

Sub Workbook_Open()​

Application.EnableEvents = False​

Call masque​

Application.EnableEvents = True​

End Sub​

​

Sub Workbook_Activate()​

Application.EnableEvents = False​

Call masque​

Application.EnableEvents = True​

End Sub​

​

Sub Workbook_Deactivate()​

Application.EnableEvents = False​

Call normal​

Application.EnableEvents = True​

End Sub​

​

Sub Workbook_BeforeClose(Cancel As Boolean)​

Application.EnableEvents = False​

Call normal​

Application.EnableEvents = True​

ThisWorkbook.Saved = True​

End Sub​

In module1

Sub masque()​

Application.ScreenUpdating = False​

Application.EnableEvents = False​

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"​

ActiveWindow.DisplayHeadings = False​

ActiveWindow.DisplayGridlines = False​

Application.DisplayFullScreen = True​

Application.DisplayStatusBar = Not Application.DisplayStatusBar​

Application.WindowState = xlMaximized​

ActiveWindow.WindowState = xlMaximized​

Application.DisplayFormulaBar = False​

Application.EnableEvents = True​

Application.ScreenUpdating = True​

End Sub​

In module2

Sub normal()​

Application.ScreenUpdating = False​

ActiveWindow.View = xlNormalView​

ActiveWindow.DisplayHeadings = True​

ActiveWindow.DisplayGridlines = True​

Application.DisplayStatusBar = True​

ActiveWindow.DisplayHorizontalScrollBar = True​

ActiveWindow.DisplayVerticalScrollBar = True​

Application.DisplayFullScreen = False​

Application.DisplayFormulaBar = True​

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"​

'Curiously, if we put the previous line at the beginning of the module, it is not taken into account each time ...​

Application.ScreenUpdating = True​

End Sub​

In each sheet :

Sub Worksheet_Open()​

Call masque​

End Sub​

​

Sub Worksheet_Activate()​

Application.ScreenUpdating = False​

Call masque​

Application.ScreenUpdating = True​

End Sub​

' Here I've deleted the Deactivate code

​

Sub Worksheet_BeforeClose(Cancel As Boolean)​

Call normal​

End Sub​

At the beginning, it gets full screen and EXCEL appears at least for all sheets in the workbook
When it is closed, or another spreadsheet is opened, this last appears in the usual environment EXCEL.


thank you

 

[Gnaske]

Hi MrsAlice

First of all I'm not a VBA geek, but I had the same problem with Application.DisplayFormulaBar = False

I found the FormulaBar is connected to Excels Ribbon and not to a specific workbook, which mean if you open an empty Excel, the FormulaBar will be visible.
If you want to remove the FormulaBar using Excels settings, is it global in all Excels you open from your computer and the same if you use the setting in the Ribbon.
The Grid, Heading, Scroll etc. is in the present workbook and will not have any global effect at Excel.

If you place the Application.DisplayFormulaBar = False in ThisWorkbook, it will only be in the present workbook.
Alternatively you can use Andy Pope's Ribbon Editor and with some special codes, you can hide Excels Ribbon and make you own and all above.

Old Editor with a lot of examples at the site Visual Ribbon Editor
New Editor with more features and a lot of other examples at the site Visual Ribbon Editor 2010

I wanted to insert some pictures and attach a little Test Workbook to show how the editor works, but it's not possible in this Forum.
If you want to see the Test Workbook, then please write to my private mail iha@live.dk

Ib

 

[Gnaske]

Hi MrsAlice

Sorry - I posted the same reply two times and I can't delete it in this Forum either.
Hmmmmmmm.

Ib