VBA to do full screen, no headings, no formula bar etc etc

[manc]

Good morning Demigods,

I am after a short bit of vba to do the following,



a) on open, the workbork is displayed full screen, no formula bar, no headings, no gridlines, no sheet tabs

I will also have a button, where above code would be assigned to, and user could toggle between full screen and normal view.




Thanks for your viewing and digestion time taken reading this post. Any help/ideas muchly appreciated.

Regards
manc

 

[xld]

Do it in Excel with the macro recorder on, that will give you your code.

 

[Trevor G]

You need something likie this.

Sub HideStuff()

Application.DisplayFullScreen = True
Application.DisplayFormulaBar = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
End Sub

 

[manc]

Xld, i have thought this also.

But how would I stop the macro in full screen mode. Do you know the key shortcut to stop macros please?

Regards
manc

 

[manc]

Trevor G, that's great, thanks.

Couple of questions though...

How do I assign it to work automatically when workbook opens?


If the code you submitted is assigned to 'a' button, how can i get it to return back to how the screen was before using the same 'a' button, i.e. click it once it Full screens etc, click it again and it reverts back.

Suggestions appreciated
Regards
manc

 

[Trevor G]

Is it a particular workbook if so you can open that workbook and in tghe VBA screen (Use alt & F11) you should see an item in the project window stating This Workbook, if you double click it the screen to the right will give you a couple of dropdowns Click the General (first on the left) and select Workbook

add the code there

If you want to run it for a book from another then add this line

workbooks.open "Add the path to the workbook and extension.xls"
Then add the code below it.

If you want to reset then use the same method as sent but change the word from True to False or False to True

 

[manc]

Thanks Trevor.

Using your code and workbook open() suggestion and how to apply it, i've managed to do what I wanted, including toggle between full screen and normal view. Thanks for your time.

Regards
manc

 

[NIDHIN B]

Thank you. I have used the below codes for hide and unhide ribbon and formula bar etc., but the challenge is that the code is affecting other excels after opened my same VBA book. Even If I hit Esc button it is not getting off. Please advise. my requirement is when I open my VBA book all ribbon , Formula bar display tabs should be hide and if I hit Esc button then it should display the normal excel.
Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
End Sub
Sub unhide()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayWorkbookTabs = True
End Sub

 

[SALAMKK]

Thank you. I have used the below codes for hide and unhide ribbon and formula bar etc., but the challenge is that the code is affecting other excels after opened my same VBA book. Even If I hit Esc button it is not getting off. Please advise. my requirement is when I open my VBA book all ribbon , Formula bar display tabs should be hide and if I hit Esc button then it should display the normal excel.
Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
End Sub
Sub unhide()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayWorkbookTabs = True
End Sub

try this code and put in thisworkbook module

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
ActiveWindow.DisplayWorkbookTabs = True
Application.ScreenUpdating = True
End Sub

 

[SALAMKK]

this code is not affecting another excel workbook.