Hiding all toolbars

Hi,

Is there a way to hide all toolbars in a particular workbook, that will not
effect any other excel files, even if they are open at the same time?

If possible, I want to hide absolutely everything, including the menu bar.

Cheers,

You could use the workbook activate and deactivate events.

First add a new worksheet called TB. Then paste the following code into the
workbook code sheet - right click on the Excel Logo to the left of File menu
and select view code to get to the workbook code sheet. The code keeps track
of which toolbars were displayed so that these can be restored when you
deactivate the book. You can't hide the Menu Bar completely (I don't think)
but you can delete all the menu items off it.

Hope this helps
Rowan

Private Sub Workbook_Activate()

Dim tbSheet As Worksheet
Dim tbCount As Integer
Dim tb As CommandBar
Dim ctr As CommandBarPopup

Set tbSheet = Sheets("TB")
tbSheet.Range("A:A").ClearContents
tbSheet.Visible = xlSheetHidden

tbCount = 0
For Each tb In Application.CommandBars
If tb.Type = msoBarTypeNormal Then
If tb.Visible Then
tbCount = tbCount + 1
tbSheet.Cells(tbCount, 1).Value = tb.Name
tb.Visible = False
End If
End If
Next tb

For Each ctr In Application.CommandBars(1).Controls
ctr.Delete
Next ctr

Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False


End Sub

Private Sub Workbook_Deactivate()

Dim tbCount As Integer
Dim tb As String
Dim tbSheet As Worksheet
Set tbSheet = Sheets("TB")

tbCount = 1
tb = tbSheet.Cells(tbCount, 1).Value
Do While tb <> ""
Application.CommandBars(tb).Visible = True
tbCount = tbCount + 1
tb = tbSheet.Cells(tbCount, 1).Value
Loop

Application.CommandBars(1).Reset
Application.DisplayStatusBar = True
Application.DisplayFormulaBar = True

End Sub


"Iain" wrote:

> Hi,
>
> Is there a way to hide all toolbars in a particular workbook, that will not
> effect any other excel files, even if they are open at the same time?
>
> If possible, I want to hide absolutely everything, including the menu bar.
>
> Cheers,
>
>

Rowan
I have copied your code and it works.
However it also places the Formula bar and the status bar when I open up my
next workbook - both of which are never visible when I exit excel. so either
your code is memerisong something which isn't there or is placing the
toolbars on exiting.
Any ideas how to stop this ?
Mike

"Rowan" wrote:

> You could use the workbook activate and deactivate events.
>
> First add a new worksheet called TB. Then paste the following code into the
> workbook code sheet - right click on the Excel Logo to the left of File menu
> and select view code to get to the workbook code sheet. The code keeps track
> of which toolbars were displayed so that these can be restored when you
> deactivate the book. You can't hide the Menu Bar completely (I don't think)
> but you can delete all the menu items off it.
>
> Hope this helps
> Rowan
>
> Private Sub Workbook_Activate()
>
> Dim tbSheet As Worksheet
> Dim tbCount As Integer
> Dim tb As CommandBar
> Dim ctr As CommandBarPopup
>
> Set tbSheet = Sheets("TB")
> tbSheet.Range("A:A").ClearContents
> tbSheet.Visible = xlSheetHidden
>
> tbCount = 0
> For Each tb In Application.CommandBars
> If tb.Type = msoBarTypeNormal Then
> If tb.Visible Then
> tbCount = tbCount + 1
> tbSheet.Cells(tbCount, 1).Value = tb.Name
> tb.Visible = False
> End If
> End If
> Next tb
>
> For Each ctr In Application.CommandBars(1).Controls
> ctr.Delete
> Next ctr
>
> Application.DisplayStatusBar = False
> Application.DisplayFormulaBar = False
>
>
> End Sub
>
> Private Sub Workbook_Deactivate()
>
> Dim tbCount As Integer
> Dim tb As String
> Dim tbSheet As Worksheet
> Set tbSheet = Sheets("TB")
>
> tbCount = 1
> tb = tbSheet.Cells(tbCount, 1).Value
> Do While tb <> ""
> Application.CommandBars(tb).Visible = True
> tbCount = tbCount + 1
> tb = tbSheet.Cells(tbCount, 1).Value
> Loop
>
> Application.CommandBars(1).Reset
> Application.DisplayStatusBar = True
> Application.DisplayFormulaBar = True
>
> End Sub
>
>
> "Iain" wrote:
>
> > Hi,
> >
> > Is there a way to hide all toolbars in a particular workbook, that will not
> > effect any other excel files, even if they are open at the same time?
> >
> > If possible, I want to hide absolutely everything, including the menu bar.
> >
> > Cheers,
> >
> >

Dim sStatusBar
Dim sFormulaBar

Private Sub Workbook_Activate()

Dim tbSheet As Worksheet
Dim tbCount As Integer
Dim tb As CommandBar
Dim ctr As CommandBarPopup

Set tbSheet = Sheets("TB")
tbSheet.Range("A:A").ClearContents
tbSheet.Visible = xlSheetHidden

tbCount = 0
For Each tb In Application.CommandBars
If tb.Type = msoBarTypeNormal Then
If tb.Visible Then
tbCount = tbCount + 1
tbSheet.Cells(tbCount, 1).Value = tb.Name
tb.Visible = False
End If
End If
Next tb

For Each ctr In Application.CommandBars(1).Controls
ctr.Delete
Next ctr

sStatusBar = Application.DisplayStatusBar
sFormulaBar = Application.DisplayFormulaBar
Application.DisplayStatusBar = False
Application.DisplayFormulaBar = False


End Sub

Private Sub Workbook_Deactivate()

Dim tbCount As Integer
Dim tb As String
Dim tbSheet As Worksheet
Set tbSheet = Sheets("TB")

tbCount = 1
tb = tbSheet.Cells(tbCount, 1).Value
Do While tb <> ""
Application.CommandBars(tb).Visible = True
tbCount = tbCount + 1
tb = tbSheet.Cells(tbCount, 1).Value
Loop

Application.CommandBars(1).Reset
Application.DisplayStatusBar = sStatusBar
Application.DisplayFormulaBar = sFormulaBar

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mike" <[email protected]> wrote in message
news:[email protected]...
> Rowan
> I have copied your code and it works.
> However it also places the Formula bar and the status bar when I open up
my
> next workbook - both of which are never visible when I exit excel. so
either
> your code is memerisong something which isn't there or is placing the
> toolbars on exiting.
> Any ideas how to stop this ?
> Mike
>
> "Rowan" wrote:
>
> > You could use the workbook activate and deactivate events.
> >
> > First add a new worksheet called TB. Then paste the following code into
the
> > workbook code sheet - right click on the Excel Logo to the left of File
menu
> > and select view code to get to the workbook code sheet. The code keeps
track
> > of which toolbars were displayed so that these can be restored when you
> > deactivate the book. You can't hide the Menu Bar completely (I don't
think)
> > but you can delete all the menu items off it.
> >
> > Hope this helps
> > Rowan
> >
> > Private Sub Workbook_Activate()
> >
> > Dim tbSheet As Worksheet
> > Dim tbCount As Integer
> > Dim tb As CommandBar
> > Dim ctr As CommandBarPopup
> >
> > Set tbSheet = Sheets("TB")
> > tbSheet.Range("A:A").ClearContents
> > tbSheet.Visible = xlSheetHidden
> >
> > tbCount = 0
> > For Each tb In Application.CommandBars
> > If tb.Type = msoBarTypeNormal Then
> > If tb.Visible Then
> > tbCount = tbCount + 1
> > tbSheet.Cells(tbCount, 1).Value = tb.Name
> > tb.Visible = False
> > End If
> > End If
> > Next tb
> >
> > For Each ctr In Application.CommandBars(1).Controls
> > ctr.Delete
> > Next ctr
> >
> > Application.DisplayStatusBar = False
> > Application.DisplayFormulaBar = False
> >
> >
> > End Sub
> >
> > Private Sub Workbook_Deactivate()
> >
> > Dim tbCount As Integer
> > Dim tb As String
> > Dim tbSheet As Worksheet
> > Set tbSheet = Sheets("TB")
> >
> > tbCount = 1
> > tb = tbSheet.Cells(tbCount, 1).Value
> > Do While tb <> ""
> > Application.CommandBars(tb).Visible = True
> > tbCount = tbCount + 1
> > tb = tbSheet.Cells(tbCount, 1).Value
> > Loop
> >
> > Application.CommandBars(1).Reset
> > Application.DisplayStatusBar = True
> > Application.DisplayFormulaBar = True
> >
> > End Sub
> >
> >
> > "Iain" wrote:
> >
> > > Hi,
> > >
> > > Is there a way to hide all toolbars in a particular workbook, that
will not
> > > effect any other excel files, even if they are open at the same time?
> > >
> > > If possible, I want to hide absolutely everything, including the menu
bar.
> > >
> > > Cheers,
> > >
> > >