Hide and restore menus and toolbars

Linus_99

Board Regular
Joined
Aug 28, 2002
Messages
145
When starting an application, I would like to hide all menus and toolbars, and run the application full-screen. Then, on closing the application, I would like to restore the menus & toolbars to their original state.

VBA code for this would be appreciated. Thanks.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Linus,

Welcome to the board. :)

The following should get you started in the right direction. It is taken from Dave Hawley's site here: http://www.microsoftexceltraining.com/

Hide all of Excels standard Menus and Toolbars and show only your Custom Toolbar.
This code will decide if the user has closed your Workbook or simply Activated another. This code (unless changed) assumes you have a Custom Toolbar called "MyToolBar" which is attached to the Workbook. Whenever the user closes or deactivates the Workbook, all Toolbars and Menubars will be restored as before.

To attach your a Custom Toolbar go to View>Toolbars>Customize-Attach then Copy your Custom Toolbar to the Workbook.


'Module level declaration
Dim IsClosed As Boolean, IsOpen As Boolean

Private Sub Workbook_Activate()
'Show the Custom toolbar
IsClosed = False
If IsOpen = False Then
Application.ScreenUpdating = False
Run "HideMenus"
Application.ScreenUpdating = True
End If
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = True 'Closing so set to True
If Cancel = True Then IsClosed = False 'Changed their mind
End Sub


Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
IsOpen = False

On Error Resume Next 'In case it's already gone.
If IsClosed = True Then 'Workbook is closing.
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
.Delete
End With
Run "ShowMenus"
Else 'They have only activated another Workbook
Run "ShowMenus"
End If
Application.ScreenUpdating = True
End Sub


--------------------------------------------------------------------------------
The code below here must be placed within a Standard Module.
It also assumes you have a hidden sheet with the CodeName of Sheet3.
--------------------------------------------------------------------------------

'Module level declaration
Dim Allbars As CommandBar
Dim i As Integer, BarName As String
Dim FormulaShow As Boolean

Sub HideMenus()
i = 0
Sheet3.Range("C1:C50").Clear
On Error Resume Next
For Each Allbars In Application.CommandBars
If Allbars.Visible = True Then
i = i + 1
With Sheet3
.Cells(i, 3) = Allbars.Name
If Allbars.Name = "Worksheet Menu Bar" Then
Allbars.Enabled = False
Else
Allbars.Visible = False
End If
End With
End If
Next
Application.DisplayFormulaBar = False
With Application.CommandBars("MyToolBar")
.Visible = True
.Position = msoBarTop
.Left = 0
.Protection = msoBarNoMove
End With
On Error GoTo 0
End Sub


Sub ShowMenus()
On Error Resume Next
With Sheet3
For i = 1 To WorksheetFunction.CountA(.Columns(3))
BarName = .Cells(i, 3)
Application.CommandBars(BarName).Enabled = True
Application.CommandBars(BarName).Visible = True
Next i
i = 1
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
.Visible = False
End With
Application.DisplayFormulaBar = True
End With
On Error GoTo 0
Application.CommandBars("Worksheet menu bar").Enabled = True
End Sub


As for the full-screen, try something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayFullScreen = False
End Sub

Private Sub Workbook_Open()
Application.DisplayFullScreen = True
End Sub


HTH - any problems with the implementation of the code, repost on this thread. Good luck.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,582
Welcome to the board Linus.

You can try this

'To Hide
With Application
.DisplayFullScreen = True
.CommandBars("Full Screen").Visible = False
.CommandBars(1).Enabled = False
End With

'to Reset
With Application
.CommandBars("Full Screen").Visible = True
.DisplayFullScreen = False
.CommandBars(1).Enabled = True
End With

I hope this works.

Suat
 
L

Legacy 98055

Guest
Just another option.
This will hide everything including the Worksheet Menu Bar, save the settings of each bar to a temporary file(just in case), and then restore all using the original settings. Copy into a standard module:<pre>
Sub SaveHideMenus()
Dim cb As CommandBar, f As Integer
f = FreeFile
Open ActiveWorkbook.Path & "/MenuData.txt" For Output As #f
For Each cb In ThisWorkbook.Application.CommandBars
'to hide only visible bars, change to cb.Visible
If cb.Enabled Then
Write #f, cb.Name, cb.Top, cb.Left, cb.Height, cb.Width, cb.Position
cb.Enabled = False
End If
Next
Close #f
OtherDisplayed False
End Sub

Sub RestoreShowMenus()
Dim cb As CommandBar, f As Integer, cbName As String, Props(5) As Long
On Error Resume Next
f = FreeFile
Open ActiveWorkbook.Path & "/MenuData.txt" For Input As #f
Do Until EOF(f)
Input #f, cbName, Props(1), Props(2), Props(3), Props(4), Props(5)
With ThisWorkbook.Application.CommandBars(cbName)
.Enabled = True
.Top = Props(1)
.Left = Props(2)
.Height = Props(3)
.Width = Props(4)
.Position = Props(5)
End With
Loop
Close #f
Kill ActiveWorkbook.Path & "/MenuData.txt"
OtherDisplayed True
End Sub

Sub OtherDisplayed(HideStuff As Boolean)
Application.WindowState = xlMaximized
With ActiveWindow
.DisplayGridlines = HideStuff
.DisplayHeadings = HideStuff
.DisplayOutline = HideStuff
.DisplayZeros = HideStuff
.DisplayHorizontalScrollBar = HideStuff
.DisplayVerticalScrollBar = HideStuff
.DisplayWorkbookTabs = HideStuff
End With
With Application
.DisplayFormulaBar = HideStuff
.DisplayStatusBar = HideStuff
.ShowWindowsInTaskbar = HideStuff
End With
End Sub</pre>

Call as such from your Workbook module or however you want to use it.<pre>
Dim SkipEvent As Boolean

Private Sub Workbook_Activate()
If SkipEvent Then
SkipEvent = Not SkipEvent
Exit Sub
End If
SaveHideMenus
End Sub

Private Sub Workbook_Deactivate()
If SkipEvent Then Exit Sub
RestoreShowMenus
End Sub

Private Sub Workbook_Open()
SkipEvent = True
SaveHideMenus
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
SkipEvent = True
RestoreShowMenus
DoEvents
End Sub</pre>
Tom
This message was edited by TsTom on 2002-08-29 02:44
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329

ADVERTISEMENT

Hey Tom,

Cool code! :cool:

Are there any advantages to saving data as text files?
 
L

Legacy 98055

Guest
I don't know if there are any real advantages to using text files? Personal preference to keep some of my workbooks smaller and uncluttered. Also the files are more easily available to other applications if that applies.
Tom
 

chef

Well-known Member
Joined
Jul 10, 2002
Messages
568
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

I am not sure what I have done here...but probably a classic case of dabbling in soemthing I don't know how to Fix.

I copied this code from Tom...pasted this into 2 modules then saved it in a file called hide.xls...

When I open up excel now ...all the toolbars etc have disappeared and I cannot seem to get them back...have reinstalled excel 2000 but same thing.

Anyone help me get excels functionality back?

regards
chef
 

Linus_99

Board Regular
Joined
Aug 28, 2002
Messages
145
Hi Chef,

Yes, the same thing happened to me.

I managed to recover it by taking the text file that is saved: MenuData.txt onto another computer with Excel, starting up a new Excel workbook, & running just the RestoreShowMenus() sub. I then moved that workbook onto the original computer, & the menus were restored.

This sounds a bit "clugey" but it worked.

I then tried the code suggested by Richie, & it worked OK. It seems a lot simpler, however if you shut down the application while it is running, you can have your menus screwed up - but they are recoverable !!!
 

Forum statistics

Threads
1,148,055
Messages
5,744,539
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top