Hi All

I'm hoping somebody could furnish me with / point me to some VB script in order to hide all the toolbars (including the File Menu) in Excel when I open a particular workbook, but that doesn't leave Excel without toolbars upon exit of that workbook

Many thanks in anticipation

Jonathan

Jonathan,

You should check out the previous posts, I have already answered (a very
similar question) today. Here is that code, modified for your needs

Here is some code to hide them and restore them. This code would go in the
ThisWorkbook code module. Your code to create the special CBs should be run
before you hide the others.

Dim aryCBs

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long

For i = LBound(aryCBs) To UBound(aryCBs)
If oCB.Name = "Worksheet Menu Bar" Then
Application.CommandBars(aryCBs(i)).Enabled = True
Else
Application.CommandBars(aryCBs(i)).Visible = True
End If
Next i

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim i As Long

ReDim aryCBs(0)
For Each oCB In Application.CommandBars
If oCB.Visible Then
If oCB.Name = "Worksheet Menu Bar" Then
oCb.Enabled = False
Else
oCB.Visible = False
End If
ReDim Preserve aryCBs(i)
aryCBs(i) = oCB.Name
i = i + 1
End If
Next oCB

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jonathan" wrote in message
...
Hi All,

I'm hoping somebody could furnish me with / point me to some VB script in
order to hide all the toolbars (including the File Menu) in Excel when I
open a particular workbook, but that doesn't leave Excel without toolbars
upon exit of that workbook.

Many thanks in anticipation,

Jonathan

Many Thanks Bob

Jonathan

how come excel does not reconize the declaration:

As CommandBar

I am using Excel 2000

Thank

--
Message posted from http://www.ExcelForum.com

Hi all

Below is the code I have used to hide/unhide all the toolbars in excel


It falls in that the tabs at the end of the page seem to be hidde
below the taskbar containing my Start button, timer etc.

Also, I have an exit button which simply calls ActiveWorkbook.Close
but it does not fully close the instance of excel, it closes th
workbook and hides the X button on the top right of the screen.

Any ideas? Thanks in advance. My code is:



Dim CurrentToolSet(20) As Boolean
' Static Flag As Boolean
Dim i As Integer
Private Sub Workbook_Open()
Application.DisplayFullScreen = True
Application.CommandBars("Full Screen").Enabled = True

Application.CommandBars.ActiveMenuBar.Enabled = False
ActiveWindow.DisplayHeadings = False
' Loop through all of the toolbars.
For i = 1 To Application.Toolbars.Count

' Restore toolbar setting to the original value as saved in
' the array.
CurrentToolSet(i) = Application.Toolbars(i).Visible
Application.Toolbars(i).Visible = False
' End of loop.
Next i
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'This command, coupled with setting the screen to full screen an
hiding the full screen toolbar will give you
'a non-Excel look:
Application.DisplayFullScreen = False
Application.CommandBars("Full Screen").Enabled = False

Application.CommandBars.ActiveMenuBar.Enabled = True
ActiveWindow.DisplayHeadings = True
' Loop through all of the toolbars.
For i = 1 To Application.Toolbars.Count

' Store the visible property of each toolbar in the array
' CurrentToolSet.
Application.Toolbars(i).Visible = CurrentToolSet(i)
' End of loop.
Next i
Application.WindowState = xlNormal
Application.WindowState = xlMaximized

End Su

--
Message posted from http://www.ExcelForum.com