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.

 

[Gnaske]

Hi again MrsAlice.

I was editing in my last message, when I was thrown off and when back, it was too late to edit anymore.

Try this code in the VBA ThisWorkbook.
I believe it would work and hide the FormulaBar when it's opened and show it again, when it's closed.

Private Sub Workbook_Activate()

    Application.DisplayFormulaBar = False

End Sub

Private Sub Workbook_Deactivate()

    Application.DisplayFormulaBar = True

End Sub

 

[x_swinson_x]

[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

This has worked great for what I need!

The only issue I'm having is the workbook will no longer allow the copy and paste function.

Is there a way to tweak this so ctrl+c and ctrl+v will still work?

 

[comarastaman]

Hi,

I'm new here and I'm having the same issue as you have and this code worked with me however, I'm not able to copy nor paste contents on the workbook I'm working on.

Can you tell me if there's anything I can tweak on your code to make this work?

I add a correction to last post

 

[Gnaske]

Hi.

I tried the code from x_swinson_x and it works.

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

Ctrl+C and Ctrl+V works for me too.

I use Office / Excel 2010.

 

[comarastaman]

Hi,

I've tried x_swinson_x code and it worked great with me. I've been adding the code in my ThisWorkbook code field and it seems that it doesn't work that way. When I created a separate module for it, it worked fine.

Hi.

I tried the code from x_swinson_x and it works.

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

Ctrl+C and Ctrl+V works for me too.

I use Office / Excel 2010.

 

[Gnaske]

Comarastaman - Do you use Sub Activate and Sub Deactivate in ThisWorkbook ?

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

 

[comarastaman]

Yes, I just copied and pasted the code here. It works sometimes, sometimes it doesn't though.

Comarastaman - Do you use Sub Activate and Sub Deactivate in ThisWorkbook ?

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

 

[Gnaske]

Comarastaman - Hmmm.... Sound weird

Do you have other workbooks open at the same time to conflict ?
If not, I don't have an explanation, why it only works sometime in ThisWorkbook.

Anyway, if the codes work in a module, it should be fine

 

[guguptb]

[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

This is resolved but u created another problem, same mine, that i am trying to solve.

When u use workbook_actavte and deactivate eand u want to copy and paste between differents workbooks, the macro execution automaticaly set cutcopymode to false and you is unable to copy to or for different workbooks. I am seeking for a way to copy to clipboard befor deactivate.... bu i couldnt find a solution till now...