Macro VBA to hide all toolbars etc upon workbook opening

[APML]

Hi All, let me start by saying I’m not very good at VBA but I do manage.

I have a workbook that has 8 worksheets (tabs) and 2 hidden worksheets (tabs)

I’m wanting a macro that on opening anyone the sheet it:

Hides all toolbars, formula bars, and the grid and grid labels

And if possible make it not possible for other uses to unhide (have access to) them



All help is greatly appreciated.

 

[cmowla]

Put all of this code into a module and run the top sub (and observe):

Sub Test__Maximize_Workspace()
Call Maximize_Workspace(True)
MsgBox "Observe!"
Call Maximize_Workspace(False)
End Sub
Sub Maximize_Workspace(trueOrFalse As Boolean)
If trueOrFalse = True Then
    Application.DisplayStatusBar = False
    Application.DisplayFormulaBar = False
    ActiveWindow.DisplayHeadings = False
    Call Show_Top_Ribbon(False)
Else
    Application.DisplayStatusBar = True
    Application.DisplayFormulaBar = True
    ActiveWindow.DisplayHeadings = True
    Call Show_Top_Ribbon(True)
End If
End Sub
Sub Test__Show_Top_Ribbon()
Call Show_Top_Ribbon(True)
End Sub
Sub Show_Top_Ribbon(hideUnhide As Boolean)
If hideUnhide = True Then
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
Else
    Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",False)"
End If
End Sub

Is that what you want? If so, I don't know how to make it so that the user cannot undo it. I will leave that part for someone else to answer.

 

[APML]

Hey thx for your help, will test now, just 1 question.. when you say "and run the top sub (and observe)", what do you mean?

 

[cmowla]

Run this sub:

Sub Test__Maximize_Workspace

 

[APML]

wow, does exactly what I wanted it to do except after I hit ok on the message box it all goes back to normal (as in toolbars etc are back), is there a way for that not to happen?

 

[cmowla]

Yes, that's just a test sub. It is supposed to just show you (briefly) what it looks like and then undoes what it did. But if you want to be "permanent", simply modify the top sub (that you just ran) to:

Sub Test__Maximize_Workspace()
Call Maximize_Workspace(True)
End Sub

Also, if you want it to also hide the sheet tabs, add this line of code to the mix of related code lines:

ActiveWindow.DisplayWorkbookTabs = False

That is, modify the sub below it to become:

Sub Maximize_Workspace(trueOrFalse As Boolean)
If trueOrFalse = True Then
    Application.DisplayStatusBar = False
    Application.DisplayFormulaBar = False
    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayWorkbookTabs = False
    Call Show_Top_Ribbon(False)
Else
    Application.DisplayStatusBar = True
    Application.DisplayFormulaBar = True
    ActiveWindow.DisplayHeadings = True
    ActiveWindow.DisplayWorkbookTabs = True
    Call Show_Top_Ribbon(True)
End If
End Sub

 

[APML]

Fantastic, have tested and works great... but when I close the workbook and reopen then it goes back to normal (as in toolbars, etc are visible), is there a way to trigger the macro upon opening the workbook?

 

[cmowla]

I assumed that you knew of the ThisWorkbook module:

Double click on that, and copy the following code into there, save the Workbook, close it, open it, and see what happens! (Keep all other code you had previously. This is extra.)

Private Sub Workbook_Open()
Call Maximize_Workspace(True)
End Sub

 

[APML]

I assumed that you knew of the ThisWorkbook module: View attachment 74434
Double click on that, and copy the following code into there, save the Workbook, close it, open it, and see what happens! (Keep all other code you had previously. This is extra.)

Private Sub Workbook_Open()
Call Maximize_Workspace(True)
End Sub

Yes, it works...........I'm a bit of a hack and not very good at VBA.. one last question, hope I'm not pushing my luck ..
is there a way like maybe a shortcut key that I can add so if I want to see all toolbars etc just hit the short cut and It reverts back to normal (was thinking ctrl z )

 

[cmowla]

Sure. (Well, the question is, will it work for you. It works for me . . . )

Modify the sub I just mentioned to you to:

Private Sub Workbook_Open()
Call Maximize_Workspace(True)
Application.OnKey "^z", "UnMaximize_Workspace"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable_Key
End Sub

Then add these additional subs to the normal module (where all of the other code is):

Sub Disable_Key()
Application.OnKey "^z", "Do_Nothing"
End Sub
Sub Do_Nothing()
'Do nothing
End Sub
Sub UnMaximize_Workspace()
Call Maximize_Workspace(False)
End Sub

The "catch" is, Ctrl Z is normally for undo, right? So we have to unassign the key before we close the workbook (to not wreck Excel's default keys). But if any workbooks were opened before you closed this one, the key will still be assigned (and it will actually open this workbook and run the unhide ribbon, etc., if you press ctrl z in another workbook!) You will need to close all of them and start anew before Ctrl Z reverts back to undo.

But if your users will only have this Workbook opened at a time, and then maybe later have other ones opened, then this will not be an issue.