hiding toolbar code

shaker

Board Regular
Joined
Jul 19, 2002
Messages
88
hi every one
i found this code on this site.
works fine but i have two questions.
how can i modify it so it doesn't show my tool bar either.
also when i close the application and open another the toolbars are there(thats good that bit works fine) but when i close the application using my macro button(application.quit)intead of the close but on excel then try to open another work book the toolbars r missing. why is this happening. can anyone help
thnx in advance

'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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Howdy Shaker,

When I ran the following:<pre>
Option Explicit
Public Vis(1000) As Variant

Sub HideBars()
Dim n As Integer
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.DisplayFormulaBar = False
For n = 1 To Application.CommandBars.Count
Vis(n) = Application.CommandBars(n).Visible
If CommandBars(n).Visible = True Then CommandBars(n).Visible = False
Next n
End Sub

Sub ShowBars()
Dim n As Integer
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.DisplayFormulaBar = True
On Error Resume Next
For n = 1 To Application.CommandBars.Count
Application.CommandBars(n).Visible = Vis(n)
Next n
End Sub</pre><pre></pre>
"MyToolBar" was long gone...

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-07 20:36
 

shaker

Board Regular
Joined
Jul 19, 2002
Messages
88
hi Nate,
I just used this one
but when i open anoter aexcel application the toolbars are also missing there
 

Forum statistics

Threads
1,144,768
Messages
5,726,181
Members
422,660
Latest member
mrsteele

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