ExcelBanter - Startup

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Startup (https://www.excelbanter.com/excel-programming/407373-startup.html)

Hi
Is it possible to have only the menubar visible on startup with an Excel
workbook? if so what would the script look like?
cheers
Tanya

You can hide the toolbars using the code below. Insert a Module into your
project (Insert/Module from the VB editor's menu bar) and copy/paste the
code below into its code window....

'******* Start of Code *******
Public C As New Collection

Public Sub HideToolBars()
Dim T As Object
Set C = Nothing
For Each T In Toolbars
If T.Visible Then C.Add T
T.Visible = False
Next
End Sub

Public Sub ShowToolBars()
Dim T As Object
For Each T In C
T.Visible = True
Next
End Sub
'******* End of Code *******

You can call the HideToolBars subroutine in the Workbook Open event to hide
the toolbars. When you want to display them again, run the ShowToolBars
subroutine. While I haven't tested it, I would expect that if you hide the
toolbars, save the Excel workbook and then CLOSE it, that the ShowToolBars
subroutine would not restore them again (without doing so through Excel's
View/Toolbars menu item).

Rick

"Tanya" wrote in message
...
2003

"Nigel" wrote:

Which version of Excel are you using?

--

Regards,
Nigel

"Tanya" wrote in message
...
Hi
Is it possible to have only the menubar visible on startup with an
Excel
workbook? if so what would the script look like?
cheers
Tanya

You can call the HideToolBars subroutine in the Workbook Open
event to hide the toolbars.

Of course, you can the HideToolBars at any time in order to make them
invisible; I just mentioned the Workbook Open event because your initial
posting suggested you didn't want them to be seen when the workbook was
first opened.

Rick

When you want to display them again, run the ShowToolBars subroutine.

Tanya,
this should do what you want:
When workbook opens and assuming user enables macros toolbars except menubar
will not be visible.
When you close workbook, all should be restored.

Hope useful

'place code in Thisworkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Restore Bars
HideBars (xlOff)
End Sub

'place code in Thisworkbook
Private Sub Workbook_Open()
'Hide Bars
HideBars (xlOn)
End Sub

'place this code in normal module
Sub HideBars(state)
Static myoldbars As New Collection
Dim mybar

If state = xlOn Then
For Each mybar In Application.CommandBars
If mybar.Type < 1 And mybar.Visible Then
myoldbars.Add mybar
mybar.Visible = False
End If
Next mybar

Else
'restore bars
For Each mybar In myoldbars
mybar.Visible = True
Next

End If
End Sub

--
JB

"Tanya" wrote:

Hi
Is it possible to have only the menubar visible on startup with an Excel
workbook? if so what would the script look like?
cheers
Tanya

Here is a possible solution from Ozgrid.

http://www.ozgrid.com/VBA/toolbar-remove-restore.htm

Mark Ivey

"Tanya" wrote in message
...
Hi
Is it possible to have only the menubar visible on startup with an Excel
workbook? if so what would the script look like?
cheers
Tanya

Hi Rick
Thank you very much for these instructions.
Much appreciated.
cheers
Tanya

"Rick Rothstein (MVP - VB)" wrote:

You can call the HideToolBars subroutine in the Workbook Open
event to hide the toolbars.

Of course, you can the HideToolBars at any time in order to make them
invisible; I just mentioned the Workbook Open event because your initial
posting suggested you didn't want them to be seen when the workbook was
first opened.

Rick

When you want to display them again, run the ShowToolBars subroutine.