Hiding all toolbars

[Guest]

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,

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)